Virtual Columns in Oracle 11g

Virtual Columns is a new feature of Oracle 11g. It appear to be normal table columns, but their values are derived rather than being stored on disc.

Syntax:

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

Features of Virtual Columns in Oracle:

1. You can define Indexes against virtual columns as well. However, please note that indexes defined against virtual columns are equivalent to function-based indexes.
2. Virtual columns can be referenced in the column list of SELECT statements and also in the WHERE clause of SELECT, UPDATE and DELETE statements.
3. Tables containing virtual columns can still be eligible for result caching.
4. Virtual columns can be used in the partition key in all basic partitioning schemes.

Limitations of Virtual Columns in Oracle:

1. DML operations on the virtual columns are not allowed.
2. While virtual columns are candidate for indexing, virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
3. The expressions used in creating virtual columns as following limitations:

• It cannot refer to any other column of another table, It can only refer to columns defined in the same table.
• It cannot refer to another virtual column with its name although the same expression can be used.
• The column expression for virtual columns can refer to a PL/SQL function if the function is designated DETERMINISTIC during its creation.