Virtual Columns in Oracle 11g
Posted by decipherinfosys on January 23, 2008
Virtual columns is a new feature in Oracle 11g. This is something that is already available in SQL Server and DB2 LUW albeit with a different name. You can read more about computed columns (virtual columns) in one of our previous blog post here. In Oracle 10g and prior releases, the only way we can achieve computed column functionality is via a trigger. In the latest release, Oracle introduced virtual columns for which values are calculated at run time. Syntax is more like DB2 syntax. Let’s follow that up using an example:
CREATE TABLE TEST_COMPUTE
COL2 GENERATED ALWAYS AS (REVERSE(COL1)) VIRTUAL
In the above table, we are creating a virtual column with ‘GENERATED ALWAYS’ syntax. Column value is generated at run time based on the value that gets into the COL1 column of the table. Let’s create some data to see this in practice:
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘DECIPHER’);
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘INFORMATION’);
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘SYSTEMS’);
Let us check the output now which shows col2 contains reverse value of col1.
SQL> SELECT * FROM TEST_COMPUTE;
COL1 COL2 ------------ ------------ DECIPHER REHPICED INFORMATION NOITAMROFNI SYSTEMS SMETSYS
For creating virtual column(s), column expression or a function should be a deterministic function which means that for a given input, virtual column should always return the same result. If that is not the case, then we can not define virtual column for such expressions. Following is an example.
SQL> ALTER TABLE TEST_COMPUTE
ADD COL3 GENERATED ALWAYS AS (TRUNC(SYSDATE));
ERROR at line 2:
ORA-54002: only pure functions can be specified in a virtual column expression
Since we can create virtual columns only for deterministic values, we can also create index(es) on virtual columns just like regular columns. Index will be always function based index.
SQL> CREATE INDEX TEST_COMPUTE_IND_1 ON TEST_COMPUTE(COL2);
We can verify it using the following SQL.
SQL> SELECT a.index_name, a.index_Type, b.column_Expression
FROM user_indexes a
INNER JOIN user_ind_Expressions b
ON a.index_name = b.index_name
WHERE a.index_name = ‘TEST_COMPUTE_IND_1’;
Here is the output.
INDEX_NAME INDEX_TYPE COLUMN_EXPRESSION -------------------- ------------------------- -------------------- TEST_COMPUTE_IND_1 FUNCTION-BASED NORMAL REVERSE("COL1")
One can even partition on this column. There are some restrictions as well for virtual columns:
1. We cannot explicitly enter the data in virtual columns. Attempt to enter value for a column in the insert statement will result into ORA-54013 error.
2. We cannot update entire row using SET ROW command with TABLE%ROWTYPE operator. Virtual columns will not be ignored when we update entire record and hence will result into an error. Following is an example with an error.
WHERE col1 = ‘DECIPHER’;
v_testrec.COL1 := ‘ORACLE’;
SET ROW = v_testrec
WHERE col1 = ‘DECIPHER’;
ORA-54017: UPDATE operation disallowed on virtual columns
ORA-06512: at line 12
This is yet another step in the right direction – supporting an application across three different RDBMS (Oracle, MS SQL Server, DB2 LUW) would be so much more easier with such common features.
Sorry, the comment form is closed at this time.