Oracle 11g introduced the concept of ‘Virtual Column’ within a table. Virtual Columns are similar to normal table’s columns but with the following differences:
They are defined by an expression. The result of evaluation of this expression becomes the value of the column.
The values of the virtual column are not stored in the database. Rather, it’s computed at run-time when you query the data.
You can’t update (in SET clause of update statement) the values of virtual column. These are read only values, that are computed dynamically and any attempt to modify them will result into oracle error.
The syntax for defining a virtual column is:
column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]
where the parameters within  are optional and can be omitted. If you don’t mention the datatype, Oracle will decide it based on the result of the expression.
Excepting the above points, a virtual column, exists just like any other column of a normal table and the following points apply to it:
Virtual columns can be used in the WHERE clause of UPDATE and DELETE statement but they can’t be modified by DML.
Statistics can be collected on them.
They can be used as a partition key in virtual column based partitioning.
Indexes can be created on them. As you might have guessed, oracle would create function based indexes as we create on normal tables.
Constraints can be created on them.
Create table with Virtual Column
For creating a virtual column, use the syntax mentioned above. Consider the following example:
Here we have defined a virtual column “total_sal” whose value would be dynamically calculated using the expression provided after the “generated always as” clause. Please note that this declaration is different than using “default” clause for a normal column as you can’t refer column names with “default” clause.
Lets check the data dictionary view:
SELECT column_name, data_type, data_length, data_default, virtual_column
WHERE table_name = 'EMPLOYEE';
COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_DEFAULT | VIRTUAL_COLUMN
EMPL_ID | NUMBER | 22 | null | NO
EMPL_NM | VARCHAR2 | 50 | null | NO
MONTHLY_SAL | NUMBER | 22 | null | NO
BONUS | NUMBER | 22 | null | NO
TOTAL_SAL | NUMBER | 22 | "MONTHLY_SAL"*12+"BONUS" | YES
The value “YES” for the column “virtual_column” tells us that this is a virtual column. Another optional keyword “VIRTUAL” can also be added to make it syntactically complete.
We have included the “VIRTUAL” clause in the table definition. Please note that instead of using an expression, I have used a deterministic function. A deterministic function, when passed certain inputs, will always return the exact same output. “DETERMINISTIC” keyword is needed in order to mark a function as a candidate to be used in a function based index.
You can also create indexes on the virtual columns. Here is an example:
CREATEINDEX idx_total_sal ON employee(total_sal);
SELECT index_name, index_type
WHERE table_name = 'EMPLOYEE';
IDX_TOTAL_SAL FUNCTION-BASED NORMAL
Note that even this function is used as part of table definition, you can still drop it. But this in turn will make the table inaccessible.
SELECT * FROM employee;
Error at line 0
ORA-00904: "schema"."GET_EMPL_TOTAL_SAL": invalid identifier
You can alter the table with virtual column as you would modify a table with normal columns. Lets add the same column using the ALTER command:
Note that the datatype of the new column is not declared. It will be assigned a datatype based on the result of the expression (in this case, it would be NUMBER). Now let’s insert some data in the table:
Here we have populated the table columns except the virtual column with some values. Upon selecting the data, we get the value for “total_sal”. Remember that this data is not actually stored in the database but evaluated dynamically. Lets try updating this value of this virtual column:
**The query above uses a wonderful function “display_raw” by “Greg Rahn” to display the high/low values. Please check the references at the last to see it’s definition.
A virtual column can only be of scalar datatype or XMLDATATYE. It can’t be a user defined type, LOB or RAW.
All columns mentioned as part of the virtual column expression should belong to the same table.
No DMLs are allowed on the virtual columns.
The virtual column expression can’t reference any other virtual column.
Virtual columns can only be created on ordinary tables. They can’t be created on index-organized, external, object, cluster or temporary tables.
If a deterministic function is used as virtual column expression, that virtual column can’t be used as a partitioning key for virtual column-based partitioning.
Virtual Column-Based Partitioning
Prior releases of Oracle only allowed a table to be partitioned based on a physical column. Oracle 11g, with the addition of virtual columns, now allows a partition key based on an expression, using one or more existing columns of the table. A virtual column can now be used as a partitioning key. Lets partition our table based on the virtual column “total_sal”:
So far, everything looks fine, lets now try to update monthly salary of one employee and in turn the value of total_sal.
SET monthly_sal = 30000WHERE empl_id = 500;
ORA-14402: updating partition key column would cause a partition change
What happened? The reason is simple, updating the “monthly_sal” would result into change in “total_sal” of the employee and thus a partition change is required. This can be handled by enabling the row movement in the current definition of the table.
The update works fine. As mentioned before, a deterministic function can’t be used as virtual column expression which is to be used as a partitioning key. It has to be an expression defined on the columns of the table as done in the previous example. The following syntax will result in oracle error: