Inside Oracle 11g Virtual Columns!

 Author: James Eric

Page: 1 2

In prior releases of Oracle, triggers were the only way to achieve computed column functionality. Oracle 11g has blessed database administrators with a new exciting feature of Virtual Columns that allows values to be calculated at run time. A virtual column basically is an empty column that contains a function upon other table columns.

Creating Virtual Column:

The column expression or function for creating virtual column should be a deterministic function. It means that the virtual column should always return the same result for a given input. The syntax for creating virtual column as follows

CREATE TABLE MY_VIRTUAL_COLUMN
(
  MYCOL1 VARCHAR2(15),
  MYCOL2 GENERATED ALWAYS AS (REVERSE(MYCOL1)) VIRTUAL
);

Inserting values in Virtual Column:

The value of MYCOL2 will be generated at run time, based on the value of MYCOL1 and the value will be reverse of it. For example if we insert ‘VALUE' in MYCOL1, then MYCOL2 will automatically get the value of REVERSE(‘VALUE') means ‘EULAV'.

INSERT INTO MY_VIRTUAL_COLUMN(MYCOL1) VALUES(VALUE');
INSERT INTO MY_VIRTUAL_COLUMN(MYCOL1) VALUES(‘SYS');
INSERT INTO MY_VIRTUAL_COLUMN(MYCOL1) VALUES(‘NAME');

We can check the value by running SELECT statement as follows.

SQL> SELECT * FROM MY_VIRTUAL_COLUMN;

MYCOL1         MYCOL2
------------         ---------------
VALUE             EULAV
SYS                  SYS
NAME              EMAN

You can not define non-deterministic function for a virtual column. For example the Oracle/PLSQL TRUNC function that returns a date truncated to a specific unit of measure can not be used with a virtual column.

SQL> ALTER TABLE MY_VIRTUAL_COLUMN
ADD COL3 GENERATED ALWAYS AS (TRUNC(SYSDATE));

ERROR at line 2:
ORA-54002: only pure functions can be specified in a virtual column expression

Creating Indexes on Virtual Columns:

We can only create function based indexes on virtual columns.

SQL> CREATE INDEX MYINDEX ON MY_VIRTUAL_COLUMN(MYCOL2);

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 = ‘MYINDEX';

INDEX_NAME          INDEX_TYPE                                       COLUMN_EXPRESSION
--------------------       ------------------------------------------       -------------------------------------
MYINDEX                  FUNCTION-BASED NORMAL           REVERSE("MYCOL1") Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Let’s reveal the magic of Oracle Database with Web Services!!
   Oracle on Linux, Some useful commands & tips
   Amazing Tips to Fix Broken Oracle ASM Instance!!
   DBA Tips for Determining Invalid Materialized Views!!