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
Create Table Product
Product_Name Varchar2 (100),
New_Value Number Generated Always As (Value + Increment) Virtual
Create Table Product_Sales
Generated Always As
When Sls_Loc_Id = 1 Then Sls_Amt
When Sls_Loc_Id =2 Then Sls_Amt * .01
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.