Virtual Column

What is Virtual Column?

 * Virtual Column values are derived by the evaluation of:
 * Columns from the same table
 * Constants
 * Function calls (user-defined or SQL functions)
 * Virtual Columns are not physically stored but are evaluated on demand.
 * Virtual Columns can be indexed (== function-based), enforced using constraints and used in queries, DML, and DDL statements like other table column types
 * Table can be partitioned on a virtual column and even statistics can be gathered upon them.

Demo
create table EMP_COMP ( empno      NUMBER(4) not null,  ename      VARCHAR2(10),  job        VARCHAR2(9),  mgr        NUMBER(4),  hiredate   DATE,  salary     NUMBER(7,2),  comm_pct   NUMBER(3,2),  deptno     NUMBER(2),  total_comp as (salary * (1 + comm_pct)) );
 * Create a sample table with a VIRTUAL column.

Table created

SQL> create index EMP_TCOMP_IDX on EMP_COMP(total_comp); Index created
 * Virtual Column can be indexed

create table EMP_COMP ( empno      NUMBER(4) not null,  ename      VARCHAR2(10),  job        VARCHAR2(9),  mgr        NUMBER(4),  hiredate   DATE,  salary     NUMBER(7,2),  comm_pct   NUMBER(3,2),  deptno     NUMBER(2),  total_comp as (salary * (1 + comm_pct)) ) partition by range (total_comp) ( partition p1 values less than (50000),   partition p2 values less than (100000)	 :	 : );
 * Virtual Column can be used as partition key.