Virtual Column in Oracle

Oracle has introduced new feature in version 11 ie. Virtual column. Virtual column values are not stored on disk. They are generated at runtime using their associated expression.

Lets have a look how to create a table with Virtual Column 

CREATE TABLE testvirtual
(
   id1   NUMBER (10)
 , id2   NUMBER (10)
 , id3   NUMBER (10) GENERATED ALWAYS AS (id1 + id2) VIRTUAL
);
Virtual keyword is optional.


Lets insert the data into Virtual Column:

Insert Into testvirtual(id1,id2, id3)  Values(10,50, 60);

ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

Lets insert the values in another two columns now :

Insert Into testvirtual(id1,id2)  Values(10,50);

Lets have a look at the data :

SELECT * FROM testvirtual;

ID1	ID2	ID3
10	50	60

Id3 is generated at run time which is id1+id2.

Indexes On Virtual Columns :

Index can be created on Virtual columns.

              Create unique index ind_virtual on testvirtual(id3);

We can test this by inserting the same values in id1 and id2.

     Insert Into testvirtual(id1,id2)  Values(10,50);

      Error->ORA-00001: unique constraint (DWSOURCE.IND_VIRTUAL) violated

Point to be noted:

1. Virtual columns do not consume any table storage other than the small amount of metadata in the data dictionary .
2. Built-in functions such as DUMP and VSIZE will consider virtual column as normal column. They will behave as usual.
3. If we need to serach trace file with virtual column reference we need to set some parameters at db level:
_trace_virtual_columns = false
_replace_virtual_columns = true
_virtual_column_overload_allowed = true
4. It can be used in partitioned key in partitioned tables, like other table does.