Virtual Columns in Oracle Database 11g

When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below.

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.

The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form.

CREATE TABLE employees (

  id          NUMBER,

  first_name  VARCHAR2(10),

  last_name   VARCHAR2(10),

  salary      NUMBER(9,2),

  comm1       NUMBER(3),

  comm2       NUMBER(3),

  salary1     AS (ROUND(salary*(1+comm1/100),2)),

  salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,

  CONSTRAINT employees_pk PRIMARY KEY (id)

);



INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)

VALUES (1, 'JOHN', 'DOE', 100, 5, 10);



INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)

VALUES (2, 'JAYNE', 'DOE', 200, 10, 20);

COMMIT;

Querying the table shows the inserted data plus the derived commission-based salaries.

SELECT * FROM employees;



        ID FIRST_NAME LAST_NAME      SALARY      COMM1      COMM2    SALARY1    SALARY2

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

         1 JOHN       DOE               100          5         10        105        110

         2 JAYNE      DOE               200         10         20        220        240



2 rows selected.



SQL>

The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the [DBA|ALL|USER]_TAB_COLUMNS views.

COLUMN data_default FORMAT A50

SELECT column_name, data_default

FROM   user_tab_columns

WHERE  table_name = 'EMPLOYEES';



COLUMN_NAME                    DATA_DEFAULT

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

ID

FIRST_NAME

LAST_NAME

SALARY

COMM1

COMM2

SALARY1                        ROUND("SALARY"*(1+"COMM1"/100),2)

SALARY2                        ROUND("SALARY"*(1+"COMM2"/100),2)



8 rows selected.



SQL>

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.

  Here is an example of Virtual Column-Based Partitioning .

 

Virtual Column-Based Partitioning

Oracle 11g supports the concept of virtual columns on tables. 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. The example below creates a table that is list partitioned on a virtual column that represents the first letter in the username column of the table.

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')

);

The following code inserts two rows into each partition defined in the table.

INSERT INTO users (id, username) VALUES (1, 'Andy Pandy');

INSERT INTO users (id, username) VALUES (1, 'Burty Basset');

INSERT INTO users (id, username) VALUES (1, 'Harry Hill');

INSERT INTO users (id, username) VALUES (1, 'Iggy Pop');

INSERT INTO users (id, username) VALUES (1, 'Oliver Hardy');

INSERT INTO users (id, username) VALUES (1, 'Peter Pervis');

INSERT INTO users (id, username) VALUES (1, 'Veruca Salt');

INSERT INTO users (id, username) VALUES (1, 'Wiley Cyote');

COMMIT;



EXEC DBMS_STATS.gather_table_stats(USER, 'USERS');

The following query shows the data was distributed as expected.

COLUMN table_name FORMAT A25

COLUMN partition_name FORMAT A20

COLUMN high_value FORMAT A40



SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;



TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

USERS                     PART_A_G             'A', 'B', 'C', 'D', 'E', 'F', 'G'                 2

USERS                     PART_H_N             'H', 'I', 'J', 'K', 'L', 'M', 'N'                 2

USERS                     PART_O_U             'O', 'P', 'Q', 'R', 'S', 'T', 'U'                 2

USERS                     PART_V_Z             'V', 'W', 'X', 'Y', 'Z'                           2



4 rows selected.



SQL>