Oracle 11g function-based virtual columns Oracle11g Tips by Burleson Consulting

Oracle 11g has introduced a new feature that allows you to create a "virtual column", an empty column that contains a function upon other table columns (the function itself is stored in the data dictionary).

For example, assume that we have a table column named gross_pay which is defined as a function of the (hours_worked * hourly_pay_rate) columns. In this example, you cannot attempt to insert anything into the virtual gross_pay column or you will get the new error:

ORA-54013: INSERT operation disallowed on virtual columns

Traditionally this sort of "process logic" would be stored inside the application code and computed on an as-needed basis.  However, there are some benefits on placing data transformation rules inside the database itself, where it can be managed and controlled without touching the application code.  However, past attempts by Oracle to place process logic inside the database have not been met with widespread acceptance (see the Oracle8 member methods).  Possible benefits of virtual columns include:

• Automatic re-computation of derived columns for ad-hoc query tools
   

• Reduction in redundant disk space for columns that must be derived from other columns (e.g. a MONTH column that is derived from another DATE column).
   

• Easier interval partitioning

Features and limitations of 11g virtual columns

Virtual computations based on other virtual columns - Laurent Schneider also notes a limitation whereby virtual columns may not reference other virtual column values:

create table
   t
(
   x number,
   x1 as (x+1),
   x2 as (x1+1)
);

create table t(x number, x1 as (x+1), x2 as (x1+1))
*
ERROR at line 1:
ORA-54012: virtual column is referenced in a column expression

Also, it's important to note that 11g virtual columns only work within the specified table, and you cannot reference columns within other tables.

Virtual columns and 11g partitioning

However, the concept of virtual columns has the nice side effect of assisting in streamlining partitioning.  For example, assume that we have a table that is partitioned by year-month (i.e. 2007-07).  With 11g virtual columns, we can simply compute the partition key virtually, using a DATE column.  

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Need an Oracle Health Check? Do you have bad performance after an upgrade?   Need to certify that your database follows best practices? BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.