Wednesday, June 6, 2012

Oracle 11g Virtual Columns

In Oracle 11g, virtual columns in a table is based on derived data that can be used in queries. We can create indexes and collect statistics on virtual columns. Below is the the example on creating a table with virtual column

  SQL> create table emp_virtual_col
   (empno number,
   ename varchar2(22),
   sal number,
   comm number,
   gross_sal as (sal+comm));

SQL> insert into emp_virtual_col(empno,ename,sal,comm)
  2   values(1,'Bilal',1000,10);

SQL> select * from emp_virtual_col;

---------- ---------------------- ---------- ---------- ----------
         1           Bilal           1000         10       1010

Based on the result above, the Gross_sal is populated as per the formula provided in create table statement

Restrictions on virtual columns:
  • you can not write to virtual columns
  • There is no support for index-organized, external, object, cluster, or temporary tables.
  • There is no support for Oracle-supplied datatypes, user-defined types, LOBs, or LONG RAWs.
The default value of the virtual column can be viewed by running the below script.

SQL> select column_name,data_default from user_tab_columns
  2  where table_name = upper('emp_virtual_col');

No comments:

Post a Comment