Virtual Columns in oracle 11g

Virtual Columns:

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.

For creating a virtual column, use the syntax mentioned above. Consider the following example:

CREATE TABLE VTEST
(
    emp_id        NUMBER,
    emp_no        VARCHAR2(50),
    monthly_sal    NUMBER(10,2),
    bonus          NUMBER(10,2),
    tot_sal      NUMBER(10,2) GENERATED ALWAYS AS (monthly_sal*10 + bonus)
);

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
  FROM user_tab_cols
 WHERE table_name = 'VTEST';

COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_DEFAULT | VIRTUAL_COLUMN

EMP_ID                   | NUMBER      | 22         | null                     | NO           

EMP_NO                 | VARCHAR2  | 50         | null                     | NO           

MONTHLY_SAL   | NUMBER       | 22         | null                     | NO           

BONUS                   | NUMBER       | 22         | null                     | NO           

TOT_SAL           | NUMBER      | 22         | "MONTHLY_SAL"*12+"BONUS" | YES 

Lets test this :

CREATE TABLE VTEST
(
    emp_id        NUMBER,
    emp_no        VARCHAR2(50),
    monthly_sal    NUMBER(10,2),
    bonus          NUMBER(10,2),
    tot_sal      NUMBER(10,2) GENERATED ALWAYS AS (monthly_sal*10 + bonus)
);

Table created.

when you insert data into table you only insert for non virtual columns. See below


SQL> INSERT INTO VTEST (emp_id, emp_no, monthly_sal, bonus) values (1,'arvind',10000,1000);

1 row created.

SQL> INSERT INTO VTEST (emp_id, emp_no, monthly_sal, bonus) values (2,'reddy',20000,2000);

1 row created.

SQL> commit;

Commit complete.


SQL> select * from vtest;

   EMPL_ID EMPL_NM     MONTHLY_SAL BONUS   TOT_SAL
---------- ---------- ---------- ------------ ----------
         1 arvind              10000 1000     121000
         2 reddy   20000 2000     242000