Total Pageviews

March 20, 2015

3/20/2015 11:56:00 PM
Virtual Column

A virtual column is a construct that looks like a column when you describe or query a table, when you inspect meta-data, when you define indexes and constraints (except for primary key constraints). If it walks and talks like a Column, doesn’t that mean that it is a column? Well, normal columns actually store values. Virtual Columns do not! All they store is their definition. And when indexed, they have values – their expression evaluated – stored in the index. Note: virtual columns can not be set in insert and update statement

Create table with Virtual Column
For creating a virtual column, use the syntax mentioned above. Consider the following example:
CREATE TABLE EMPVT
(
   empno         NUMBER,
   ename         VARCHAR2 (50),
   msal          NUMBER (10, 2),
   bonus         NUMBER (10, 2),
   total_sal     NUMBER (10, 2)
         GENERATED ALWAYS AS (msal * 12 + bonus)
);
Here we have defined a virtual column “total_sal” whose value would be dynamically calculated using the expression provided after the “generated always as” clause. Please note that this declaration is different than using “default” clause for a normal column as you can’t refer column names with “default” clause.
Lets check the data dictionary view:
SELECT column_name, data_type, data_length, data_default, virtual_column
  FROM user_tab_cols
 WHERE table_name = 'EMPVT';

COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_DEFAULT   } VIRTUAL_COLUMN
EMPNO     | NUMBER    | 22       | null                       | NO           
ENAME     | VARCHAR2  | 50       | null                        | NO           
MSAL      | NUMBER    | 22       | null                        | NO           
BONUS     | NUMBER    | 22       | null                        | NO           
TOTAL_SAL | NUMBER    | 22       |"MSAL"*12+"BONUS" |YES            
The value “YES” for the column “virtual_column” tells us that this is a virtual column. Another optional keyword “VIRTUAL” can also be added to make it syntactically complete.
DROP TABLE EMPVT PURGE;

CREATE OR REPLACE FUNCTION get_empl_total_sal (p_msal NUMBER,
                                               p_bonus          NUMBER)
   RETURN NUMBER
   DETERMINISTIC
IS
BEGIN
   RETURN p_msal * 12 + p_bonus;
END;

CREATE TABLE EMPVT
(
   empno       NUMBER,
   ename       VARCHAR2 (50),
   msal        NUMBER (10, 2),
   bonus       NUMBER (10, 2),
   total_sal   NUMBER (10, 2) AS (get_empl_total_sal (msal, bonus)) VIRTUAL
);
We have included the “VIRTUAL” clause in the table definition. Please note that instead of using an expression, I have used a deterministic function. A deterministic function, when passed certain inputs, will always return the exact same output. “DETERMINISTIC” keyword is needed in order to mark a function as a candidate to be used in a function based index.
You can also create indexes on the virtual columns. Here is an example:
CREATE INDEX idx_total_sal ON empvt(total_sal);

SELECT   index_name, index_type
  FROM   user_indexes
 WHERE   table_name = 'EMPVT';

INDEX_NAME     INDEX_TYPE                
IDX_TOTAL_SAL  FUNCTION-BASED NORMAL
Note that even this function is used as part of table definition, you can still drop it. But this in turn will make the table inaccessible.
DROP FUNCTION get_empl_total_sal;

SELECT * FROM empvt;
*
Error at line 0
ORA-00904: "schema"."GET_EMPL_TOTAL_SAL": invalid identifier
You can alter the table with virtual column as you would modify a table with normal columns. Lets add the same column using the ALTER command:
DROP TABLE EMPVT PURGE;

CREATE TABLE EMPVT
(empno     NUMBER,
 ename     VARCHAR2(50),
 msal NUMBER(10,2),
 bonus       NUMBER(10,2)
);

ALTER TABLE EMPVT ADD (total_sal AS (msal * 12 + bonus));
Note that the datatype of the new column is not declared. It will be assigned a datatype based on the result of the expression (in this case, it would be NUMBER). Now let’s insert some data in the table:
INSERT INTO empvt (empno, ename, msal, bonus)
   WITH DATA AS
        (SELECT 100 empno, 'AAA' ename, 20000 msal, 3000 bonus
           FROM DUAL
         UNION
         SELECT 200, 'BBB', 12000, 2000
           FROM DUAL
         UNION
         SELECT 300, 'CCC', 32100, 1000
           FROM DUAL
         UNION
         SELECT 400, 'DDD', 24300, 5000
           FROM DUAL
         UNION
         SELECT 500, 'EEE', 12300, 8000
           FROM DUAL)
   SELECT *
     FROM DATA;

SELECT * FROM empvt;

EMPNO | ENAME | MSAL | BONUS | TOTAL_SAL
100     | AAA     | 20000       | 3000  | 243000
200     | BBB     | 12000       | 2000  | 146000
300     | CCC     | 32100       | 1000  | 386200
400     | DDD     | 24300       | 5000  | 296600
500     | EEE     | 12300       | 8000  | 155600
Here we have populated the table columns except the virtual column with some values. Upon selecting the data, we get the value for “total_sal”. Remember that this data is not actually stored in the database but evaluated dynamically. Lets try updating this value of this virtual column:
UPDATE empvt
   SET total_sal = 2000;

ORA-54017: UPDATE operation disallowed on virtual columns
As mentioned before, the statistics can also be gathered for the virtual columns.
EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'EMPVT');

SELECT column_name, num_distinct,
       display_raw (low_value, data_type)  low_value,
       display_raw (high_value, data_type) high_value
  FROM dba_tab_cols
 WHERE table_name = 'EMPVT';

COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE
TOTAL_SAL   | 5            | 146000    | 386200
BONUS       | 5            | 1000      | 8000
MSAL        | 5            | 12000     | 32100
ENAME       | 5            | AAA       | EEE
EMPNO       | 5            | 100       | 500

Limitations on Virtual Columns
  1. A virtual column can only be of scalar datatype or XMLDATATYE. It can’t be a user defined type, LOB or RAW.
  2. All columns mentioned as part of the virtual column expression should belong to the same table.
  3. No DMLs are allowed on the virtual columns.
  4. The virtual column expression can’t reference any other virtual column.
  5. Virtual columns can only be created on ordinary tables. They can’t be created on index-organized, external, object, cluster or temporary tables.
  6. If a deterministic function is used as virtual column expression, that virtual column can’t be used as a partitioning key for virtual column-based partitioning.


 
Related Posts Plugin for WordPress, Blogger...