How to create Virtual Columns in Oracle Database

Описание: How to create Virtual Columns in Oracle Database 12c


When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below.

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.

The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form.

CREATE TABLE employees (
id NUMBER,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
salary NUMBER(9,2),
comm1 NUMBER(3),
comm2 NUMBER(3),
salary1 AS (ROUND(salary*(1+comm1/100),2)),
salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, 'JOHN', 'DOE', 100, 5, 10);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (2, 'JAYNE', 'DOE', 200, 10, 20);
COMMIT;
Querying the table shows the inserted data plus the derived commission-based salaries.

SELECT * FROM employees;

ID FIRST_NAME LAST_NAME SALARY COMM1 COMM2 SALARY1 SALARY2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 JOHN DOE 100 5 10 105 110
2 JAYNE DOE 200 10 20 220 240

2 rows selected.

SQL
The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the [DBA|ALL|USER]_TAB_COLUMNS views.

COLUMN data_default FORMAT A50
SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';

COLUMN_NAME DATA_DEFAULT
------------------------------ --------------------------------------------------
ID
FIRST_NAME
LAST_NAME
SALARY
COMM1
COMM2
SALARY1 ROUND("SALARY"*(1+"COMM1"/100),2)
SALARY2 ROUND("SALARY"*(1+"COMM2"/100),2)

8 rows selected.

SQL
Notes and restrictions on virtual columns include:

1)Indexes defined against virtual columns are equivalent to function-based indexes.
2)Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.
3)Tables containing virtual columns can still be eligible for result caching.
4)Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled:
a)Constraint on the virtual column must be disabled and re-enabled.
b)Indexes on the virtual column must be rebuilt.
c)Materialized views that access the virtual column must be fully refreshed.
d)The result cache must be flushed if cached queries have accessed the virtual column.
e)Table statistics must be regathered.

5)Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
6)The expression used in the virtual column definition has the following restrictions:
a.It cannot refer to another virtual column by name.
b.It can only refer to columns defined in the same table.
c.If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
e.The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.