Virtual Columns

 

Virtual Columns

virtual columns look like normal table columns, but their values are derived rather than being stored on disc.  These virtual columns are not physically stored in the table, but derived from data in the table.

These virtual columns can be used in the partition key in all basic partitioning schemes.

 

create table t1 (

  id              number,

  product         varchar2(50),

  price           number(10,2),

  price_with_tax  number(10,2) generated always as (round(price*1.2,2)) virtual

);

 

insert into t1 (id, product, price) values (1, 'computer', 1500);

insert into t1 (id, product, price) values (2, 'bike', 1000);

commit;

 

select * from t1;

        ID PRODUCT              PRICE PRICE_WITH_TAX

---------- --------------- ---------- --------------

         1 computer              1500           1800

         2 bike                  1000           1200

SQL>

 

alter table t1 modify (

  price_with_tax number(10,2) as (round(price*1.3,2))

);

 

alter table t1 drop column price_with_tax;

 

The [DBA|ALL|USER]_TAB_COLS views includes columns called VIRTUAL_COLUMN and DATA_DEFAULT

 

select column_id, column_name, virtual_column, data_default

from   user_tab_cols

where  table_name = 'T1'

order by column_id;

 COLUMN_ID COLUMN_NAME          VIRTUAL_COLUMN DATA_DEFAULT

---------- -------------------- -------------- --------------------

         1 ID                   NO

         2 PRODUCT              NO

         3 PRICE                NO

         4 PRICE_WITH_TAX       YES            ROUND("PRICE"*1.2,2)

SQL>

 

Restrictions

Notes and restrictions on virtual columns include:

• Indexes defined against virtual columns are equivalent to function-based indexes.
• Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.
• Tables containing virtual columns can still be eligible for result caching.
• Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled:
  
  
• Constraint on the virtual column must be disabled and re-enabled.
• Indexes on the virtual column must be rebuilt.
• Materialized views that access the virtual column must be fully refreshed.
• The result cache must be flushed if cached queries have accessed the virtual column.
• Table statistics must be regathered.
• Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
• The expression used in the virtual column definition has the following restrictions:
  
  
• It cannot refer to another virtual column by name.
• It can only refer to columns defined in the same table.
• If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
• The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.

 

CREATE TABLE users (

  id           NUMBER,

  username     VARCHAR2(20),

  first_letter VARCHAR2(1)

    GENERATED ALWAYS AS

      (

        UPPER(SUBSTR(TRIM(username), 1, 1))

      ) VIRTUAL

)

PARTITION BY LIST (first_letter)

(

  PARTITION part_a_g VALUES ('A','B','C','D','E','F','G'),

  PARTITION part_h_n VALUES ('H','I','J','K','L','M','N'),

  PARTITION part_o_u VALUES ('O','P','Q','R','S','T','U'),

  PARTITION part_v_z VALUES ('V','W','X','Y','Z')

);