1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Virtual Columns in Oracle 11g

Discussion in 'SQL PL/SQL' started by SBH, Dec 15, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Introduction

    Virtual Columns are one of the most interesting features of Oracle 11g. It allows users to create columns whose data is not supplied by the user, but it is derived by Oracle server implicitly from other columns. Most importantly, their disk space consumption is NULL because their data is not stored in the table.

    Below are few key points about Virtual columns

    1. The virtual column and the columns to be used in the derivation of virtual column data must belong to the same table.
    2. Explicitly, data cannot be inserted into a virtual column
    3. It can be used as normal columns without any restriction. It can be constrained, indexed, and can be used in DML or DDL statements. Note that it cannot be updated in UPDATE statement.
    4. Table partitioning can be achieved based on virtual column
    5. The virtual column derivation expression appears in DATA_DEFAULT column of USER_TAB_COLUMNS dictionary view
    6. Virtual columns can be differentiated from the normal columns based on the column VIRTUAL_COLUMN in USER_TAB_COLS dictionary view. It can be queries using the below query

    Code (SQL):
    SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN
    FROM USER_TAB_COLS
    WHERE VIRTUAL_COLUMN = 'YES';
    7. It can be used for Result Caching also
    8. Temporary tables, object types, clusters, External tables and Index Organized Tables cannot have Virtual Columns

    2. Syntax

    Code (Text):
    COLUMN [DATA TYPE] [GENERATED ALWAYS] AS (EXPRESSION) [VIRTUAL]
    Virtual column carries data type of the result derived from the expression. Therefore, data type specification is optional for a virtual column, which must be of scalar data type. User defined types, LONG and LOB columns cannot be used as type of virtual columns.
    AS is the mandatory keyword, while GENERATED ALWAYS and VIRTUAL are optional, which just add meaning to the declaration. The syntax specifies that the column data must not be stored on disk.
    EXPRESSION can only contain columns of the same table. It can use constants, column values, SQL functions (not analytic or aggregate) to derive the values.

    3. Example

    Below statement creates table EMP_HISTORY with virtual columns. Note that column INVEST_AMT derives its value from ANN_SAL column.

    Code (SQL):
    CREATE TABLE EMP_INVEST (
    EMPID     NUMBER,
    ENAME VARCHAR2(100),
    ANN_SAL   NUMBER,
    SHARE_PRICE    NUMBER,
    AMT_INVEST NUMBER AS (ANN_SAL*0.01)
    )
    /

    TABLE created.
    Now inserting the test data in the table

    Code (SQL):
    INSERT INTO EMP_INVEST(EMPID, ENAME, ANN_SAL, SHARE_PRICE)
    VALUES
    (SQ_EMPID.NEXTVAL, 'CLUB-ORCL', 150000, 15);

    1 ROW inserted.
    Code (SQL):
    SELECT * FROM EMP_INVEST WHERE ENAME='CLUB-ORCL';

    EMPID         ENAME        ANN_S     AMT_IN      
    ---------         -----------         --------   ---------     
    155       CLUB-ORCL                150000    1500      
    Index can be created on Virtual Columns. They are equivalent to function based indexes.

    Code (SQL):
    SQL> CREATE INDEX IDX_INVEST_AMT ON EMP_INVEST (AMT_INVEST);

    INDEX created.
    The index information can be queried from USER_INDEXES dictionary view. Note that its type appears as FUNCTION-BASED.

    Code (SQL):
    SQL> SELECT INDEX_NAME,  INDEX_TYPE, FUNCIDX_STATUS
               FROM   USER_INDEXES
               WHERE  TABLE_NAME = 'EMP_INVEST';

    INDEX_NAME       INDEX_TYPE                         FUNCIDX_STATUS
    ------------------- ---------------------------         --------------
    IDX_INVEST_AMT         FUNCTION-BASED NORMAL               ENABLED

    1 ROW selected.
    Virtual Columns can be added to a table using ALTER TABLE command as below

    Code (SQL):
    ALTER TABLE EMP_INVEST
    ADD NUM_SHARES GENERATED ALWAYS AS (ANN_SAL*0.01*SHARE_PRICE)

    TABLE altered.
    Constraints can be added on the virtual columns as below.

    Code (SQL):
    ALTER TABLE EMP_INVEST
    ADD CONSTRAINT EMP_CHECK CHECK(NUM_SHARES != 0);