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 a sample table with a VIRTUAL column.

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

Table created

• Virtual Column can be indexed

SQL> create index EMP_TCOMP_IDX on EMP_COMP(total_comp);
 
Index created 

• Virtual Column can be used as partition key.

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)
	 :
	 :
);

See Also

• http://jonathanlewis.wordpress.com/2009/04/16/virtual-columns/
• 11g Virtual Columns and Fast Refreshable Materialized Views (What In The World) http://richardfoote.wordpress.com/2010/11/24/11g-virtual-columns-and-fast-refreshable-materialized-views-what-in-the-world/