Systems Engineering and RDBMS

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
(
COL1 VARCHAR2(15),
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.

DECLARE
V_TESTREC TEST_COMPUTE%ROWTYPE;
BEGIN

SELECT *
INTO v_testrec
FROM TEST_COMPUTE
WHERE col1 = ‘DECIPHER';

v_testrec.COL1 := ‘ORACLE';

UPDATE TEST_COMPUTE
SET ROW = v_testrec
WHERE col1 = ‘DECIPHER';

COMMIT;

END;

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.

About these ads

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 85 other followers

%d bloggers like this: