Oracle 11g function-based virtual columns

Virtual columns

Virtual columns are expressions that are based on one or more existing columns in the table. When using Virtual Column-Based Partitioning, a virtual column in a table provides advantages in disk space utilization. A virtual column is only stored as metadata. It does not consume physical space, but it can be indexed. The virtual column also contains optimizer statistics and histograms.

Using a virtual column also simplifies the use of derived columns. Transparently derived values do not require the application to calculate and insert an additional value. This also prevents the need to use a trigger on the table to provide an alternate implementation of this functionality. Using virtual columns in tables also eliminates the need to use views to display derived column values.

A new table with a virtual column can be derived using the following syntax:

create table <table_name>(
<column_name> <data_type>,
?
<column_name> [<data_type>] [generated always] as (<column_expression>) [virtual]
);

Adding a virtual column to an existing table is accomplished with the following alter table syntax:

alter table <table_name>

add (<column_name> [<data_type>] [generated always] as (<column_expression>) [virtual]);

When defining a virtual column in a table, it is possible to either include the datatype or let the database determine the datatype based on the expression.

Optionally, the phrases ?generated always? and ?virtual? can be used to help clarify the syntax. The column expression must reference columns defined on the same table; however, the column expression can refer to a PL/SQL function if the function is designated DETERMINISTIC during its creation.

For example, a virtual column might be helpful in a table that stores employee information:

SQL> create table employees(
2 employee_name varchar2(30),
3 start_date date,
4 end_date date,
5 hourly_rate generated always as (annual_salary/2080),
6 annual_salary number,
7 active as (case when end_date is null then 'Y' else 'N' end));

Table created.

SQL> insert into employees
2 (employee_name,
3 start_date,
4 end_date,
5 annual_salary)
6 values
7 ('C. TESTER', '01-JAN-2011', NULL, 100000);

1 row created.

SQL> select * from employees
2 /

EMPLOYEE_NAME START_DATE END_DATE HOURLY_RATE ANNUAL_SALARY ACTIVE
--------------- ---------- --------- ----------- ------------- ----------
C. TESTER 01-JAN-11 48.0769231 100000 Y

The INSERT statement required to create a new record in this table only requires four values, but the query of this table displays all six values. This includes the two virtual columns derived from other columns. The hourly rate virtual column is an expression of the annual salary divided by 2080 working hours per year. The second virtual column displays if the employee is active by examining the row?s end date.

Virtual columns can be used for partitioning, indexing, constraints and foreign keys. However, virtual columns cannot be used for index-organized, external, object, cluster or temporary tables.

There are several error related to virtual columns, including the ORA-12996 error.

The oerr utility shows this for the ORA-12996 error:

ORA-12996: cannot drop system-generated virtual column

Cause: An attempt was made to drop a virtual column generated by the system.

Action: None