Oracle 11g Virtual Column

Virtual Column is one of the new features introduced in Oracle 11g. Virtual column values are not stored on disk. They are generated at runtime using their associated expression

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.

virtual columns and storage

To correctly demonstrate that a virtual column uses no storage would require techniques such as block dumping, which is beyond the scope of this article. We can, however, show that adding more virtual columns to an existing table doesn't require any additional space. In the following example, we will create two tables of 10,000 records each and compare their relative sizes (one table will have several virtual columns). We will begin by creating the tables as follows.

CREATE TABLE NORML_COL_TBL

 (
 c1 VARCHAR2(4000),

 c2 VARCHAR2(4000),

 c3 VARCHAR2(4000)

);

 

CREATE TABLE succeeded.

CREATE TABLE VITTUAL_COL_TBL

 (
 c1 VARCHAR2(4000),

 c2 VARCHAR2(4000),

 c3 VARCHAR2(4000),

 c4 VARCHAR2(4000) GENERATED ALWAYS AS (UPPER(c1)) VIRTUAL,

 c5 VARCHAR2(4000) GENERATED ALWAYS AS (UPPER(c2)) VIRTUAL,

 c6 VARCHAR2(4000) GENERATED ALWAYS AS (UPPER(c3)) VIRTUAL

 );

 

 CREATE TABLE succeeded.

 

We will now load each table with 10,000 identical rows.

INSERT INTO NORML_COL_TBL

SELECT RPAD('x',4000),

        RPAD('x',4000),

        RPAD('x',4000)

FROM   dual

CONNECT BY ROWNUM <= 10000;

 10000 rows inserted

INSERT INTO VITTUAL_COL_TBL(c1,c2,c3)

SELECT RPAD('x',4000),

        RPAD('x',4000),

        RPAD('x',4000)

FROM   dual

CONNECT BY ROWNUM <= 10000;

 10000 rows inserted

Finally, we can compare the sizes of the two tables, as follows.

SELECT segment_name,

  bytes,

    blocks,

     extents

FROM   user_segments

WHERE  segment_name LIKE '%_COL_TBL%';

 

SEGMENT_NAME                                                                      BYTES                  BLOCKS                 EXTENTS               

--------------------------------------------------------------------------------- ---------------------- ---------------------- ----------------------

VITTUAL_COL_TBL                                                                 251658240            30720                 10                   

NORML_COL_TBL                                                                  251658240            30720                 10

As expected, the tables' physical storage requirements (excepting the dictionary metadata) are identical.