VIRTUAL COLUMN IN ORACLE 11G

Virtual Column is one of the new features introduced in Oracle 11g.The syntax for defining a virtual column is:

Column_Name [Datatype] [GENERATED ALWAYS] AS [Expression] [VIRTUAL]

Here, Data type: Column’s Data Type and Expression: SQL Expression

Though Virtual Columns appear to be like normal table columns they are defined by an expression and they derive their value from the result of the expression. The values of these virtual columns are not stored in database; instead they are calculated at run time dynamically.

How to create a virtual column in a table

Example 1:

Create Table Product

(

    Product_Id Number,

    Product_Name Varchar2 (100),

    Value Number,

    Increment Number,

    New_Value Number Generated Always As (Value + Increment) Virtual

);

Example 2:

Create Table Product_Sales

    (

       Sls_Key Number,

       Product_Id Number,

       Sls_Amt Number,

       Sls_Loc_Id Number,

       Sales_Percent

       Generated Always As

       (

         Case

            When Sls_Loc_Id = 1 Then Sls_Amt

            When Sls_Loc_Id =2 Then Sls_Amt * .01

            End

      ) Virtual

   );

Adding the virtual column using the ALTER command:

Alter Table Product Add (New_Value Generated Always As (Value + Increment));

You can also create indexes on the virtual columns:

­Create Index Index_New_Value On Product (New_Value);

Some important Points on Virtual Columns:

• All columns in the virtual column, should belong to the same table on which we are creating the virtual column
• We cannot do any update or DML's on Virtual column.
• Virtual column expression can’t reference any other virtual column.
• Indexes and constraints can be created on virtual columns; also they can be used as partition key
• Virtual columns reduce the use of views and derived columns.