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
- A virtual column can only be of scalar datatype or XMLDATATYE. It can’t be a user defined type, LOB or RAW.
- All columns mentioned as part of the virtual column expression should belong to the same table.
- No DMLs are allowed on the virtual columns.
- The virtual column expression can’t reference any other virtual column.
- Virtual columns can only be created on ordinary tables. They can’t be created on index-organized, external, object, cluster or temporary tables.
- 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.
