The following examples are written from memory and have not yet been tested. If someone finds a flaw in them before I get to it tomorrow, please update this page.

A new and useful feature found in Oracle 11g, virtual columns, allow you to create table columns which are purely expression-based and aren't stored on-disk. This page contains preliminary information on virtual columns including use-case identification, example usage, and recommendations.

Identifying Use-Cases for Virtual Columns

Because virtual columns are not stored on-disk, they are especially useful for fields which are based on, or calculated using, functions, expressions, or sibling fields. Several examples include commission-based salaries, full names, order totals, etc.

A Full Name Example

CREATE TABLE employees (
emp_fst_nm VARCHAR2(64),
emp_lst_nm VARCHAR2(64),
emp_midl_nm VARCHAR2(64),
emp_full_nm VARCHAR2(195),
...

In this case, we're potentially wasting up to 196 bytes (including variable length storage) for storing an employee's full name. Of course, we could always get around this with a view or by performing the expression in the SQL statement itself; neither method is as easy as using a virtual column.

emp_full_nm AS (emp_lst_nm
|| ', ' || emp_fst_nm
|| ' ' || UPPER(SUBSTR(TRIM(emp_midl_nm), 1, 1))),

A Calculated Salary

...
emp_full_nm AS (emp_lst_nm
|| ', ' || emp_fst_nm
|| ' ' || UPPER(SUBSTR(TRIM(emp_midl_nm), 1, 1))),
emp_base_salry NUMBER(9,2),
emp_comm_pct NUMBER(3),
emp_comm_salry NUMBER(9,2),
...

In this case, we're potentially wasting about 10 bytes (including variable length storage) for an easily computed salary. As before, a view could help us here, but it's not as simple as:

emp_comm_salry AS (ROUND(emp_base_salry*(1+emp_comm_pct/100), 2)),

Realistically, simply by making these two changes, we would probably save between 30 to 60 bytes per row. On this table, that isn't a significant savings. However, you could see how orders, invoices, and similar items could benefit from some space savings thereby trading a little more CPU for a little less I/O.

How to check that column is Virtual?

To identify that column is Virtual you need to check metadata view USER_TAB_COLS (or DBA_TAB_COLS or ALL_DBA_COLS) and examine the value of VIRTUAL_COLUMN.
For example use this query to list all Virtual Columns in schema:

select table_name, column_name, data_type, hidden_column
from user_tab_cols
where virtual_column = 'YES';

Please note that Oracle creates Virtual Columns when you create Function Based Index on table. Also object types (ex. XMLTYPE) are reported as Virtual Columns.

Virtual Column-based Partitioning

In progress.

Performance Considerations

While it's long been held a bad practice to SELECT * FROM a table, virtual columns will punish developers (and users of their applications) employing this practice.

As a single row query with an undefined select-list will have to calculate the value of every virtual field, significant performance implications are possible. It is therefore important that any application employing virtual columns explicitly uses queries which define only necessary fields.

Caveats and Limitations

• Virtual columns cannot be based on other virtual columns.
• Indexes on virtual columns are equivalent to function-based indexes.
• As they are purely expressions, virtual columns cannot be manipulated by DML.
• Virtual columns are not supported under external, object, cluster, temporary, or index organized tables.