Virtual Columns in Oracle

Virtual Column is a column whose value is derived from an expression. Oracle doesn't store any data related to virtual column, only expression given at the time of creating virtual column is stored in data dictionary.

The script below creates an xx_employees table which contains one virtual column as tot_sal to display the salary plus commission.

CREATE TABLE xx_employees
(
   empno     NUMBER,
   ename     VARCHAR2 (20),
   sal       NUMBER (8, 2),
   comm      NUMBER (6, 2),
   tot_sal   NUMBER GENERATED ALWAYS AS (ROUND (sal + comm)) VIRTUAL,
   CONSTRAINT empno_pk PRIMARY KEY (empno)
);

Insert data into xx_employees table as shown below. Please note that we are not inserting data into virtual column (tot_sal).

INSERT INTO xx_employees (empno, ename, sal, comm)
VALUES (1, 'TEST1', 10000, 500);

INSERT INTO xx_employees (empno, ename, sal, comm)
VALUES (2, 'TEST2', 15000, 750);

COMMIT;


Now query data from xx_employees table .
SQL>  SELECT * FROM xx_employees;

Oracle doesn't store any data related to virtual column, only expression given at the time of creating virtual column is stored in data dictionary.

Notes and restrictions on Virtual Columns:-

• 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 Data Manipulation Language.
• 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.