Virtual columns are new in Oracle 11g and allow a column to be dynamically filled with data based off an expression or function. Virtual columns do not store data on disk. Instead, the data is generated at run time. Virtual columns can be useful when there is an expression that is used on a table often. Instead of having to code the expression in the SELECT statement every time, the expression can be stored in a virtual column.
The basic syntax when creating a virtual column while creating a table is:
SQL> CREATE TABLE t1 (
2 column1 NUMBER,
3 column2 NUMBER,
4 column3 NUMBER GENERATED ALWAYS AS (column1 + column2) VIRTUAL);
Table created.The VIRTUAL keyword following the expression is not necessary, but is considered to be complete code or what Oracle calls “syntactic clarity.” Also, the data type for the virtual column is not required. Oracle with implicitly define the data type based off the return value of the expression.
Virtual columns can also be inserted into an existing table using ALTER TABLE.
SQL> CREATE TABLE t1 (
2 column1 NUMBER,
3 column2 NUMBER);
Table created.
SQL> ALTER TABLE t1
2 ADD column3 NUMBER GENERATED ALWAYS AS (column1 + column2) VIRTUAL;
Table altered.The results of example 1 and example 2 are equal.
If column1 holds the value of 20 and column2 holds the value of 10, column3 will hold the value 30 at run time. This value is not stored on disk; It is dynamically created when selected from the table t1.
SQL> INSERT INTO t1 (column1, column2) VALUES (20, 10);
1 row created.
SQL> SELECT * FROM t1;
COLUMN1 COLUMN2 COLUMN3
------- ------- -------
20 10 30Notice that no data was inserted into column3. Column3’s data was dynamically created based off the expression column1 + column2. It is impossible to explicitly insert data into column3.
SQL> INSERT INTO t1 VALUES (20, 10, 30);
INSERT INTO t1 VALUES (20, 10, 30)
* ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columnsAlso, values can only be inserted into physical columns when the physical columns are explicitly defined. The following returns an error.
SQL> INSERT INTO t1 VALUES (20, 10);
INSERT INTO t1 VALUES (20, 10)
* ERROR at line 1:
ORA-00947: not enough valuesThe virtual column is still considered to be part of the table’s column list, therefor Oracle believes that there are not enough values in this INSERT statement because only 2 values were entered when there are 3 columns. To fix this, the physical columns have to be explicitly referenced in the INSERT statement.
SQL> INSERT INTO t1 (column1, column2) VALUES (20, 10);
1 row created.Function-based Virtual Columns
Functions can be used instead of expressions in a virtual column.
SQL> CREATE FUNCTION add_columns(value1 NUMBER, value2 NUMBER)
2 RETURN NUMBER DETERMINISTIC AS
3 BEGIN
4 RETURN value1 + value2;
5 END add_columns;
6 /
Function created.
SQL> CREATE TABLE t1 (
2 column1 NUMBER,
3 column2 NUMBER,
4 column3 NUMBER GENERATED ALWAYS AS (add_columns(column1, column2))
5 );
Table created.
SQL> INSERT INTO t1 (column1, column2) VALUES (20, 10);
1 row created.
SQL> SELECT * FROM t1;
COLUMN1 COLUMN2 COLUMN3
------- ------- -------
20 10 30Notice the DETERMINISTIC keyword in the function. This is required to use the function in a virtual column. If the function add_columns is deleted, an error will occur when trying to SELECT from the table t1.
SQL> DROP FUNCTION add_columns;
Function dropped.
SQL> SELECT * FROM t1;
SELECT * FROM t1
* ERROR at line 1:
ORA-00904: "USER"."ADD_COLUMNS": invalid identifierThe virtual column now has to be altered to contain a valid expression, a valid function, or completely dropped before SELECT can be used on table t1.
Real World Application
Virtual columns are a nice way to allow data to be created dynamically instead of being hard coded. A table is created called EMPLOYEES with 4 columns that hold the employee’s ID, first name, salary, and department ID.
SQL> CREATE TABLE employees (
2 employee_id number(3),
3 first_name varchar2(20),
4 salary number (7,2),
5 department_id number(3));
Table created.
SQL> INSERT INTO employees VALUES(10, 'Mike', 4000, 100);
1 row created.
SQL> INSERT INTO employees VALUES(11, 'James', 3500, 101);
1 row created.
SQL> INSERT INTO employees VALUES(12, 'David', 2000, 100);
1 row created.If the annual salary was needed, a SELECT statement would need to be used with an expression to evaluate the annual salary.
SQL> SELECT employee_id, first_name, salary,
2 department_id, salary*12 AS ANNUAL_SALARY
3 from employees;
EMPLOYEE_ID FIRST_NAME SALARY DEPARTMENT_ID ANNUAL_SALARY
----------- ----------- ------ ------------- -------------
10 Mike 4000 100 48000
11 James 3500 101 42000
12 David 2000 100 24000This works fine, but what if the annual salary was needed nearly every time a query was called on the table EMPLOYEES? One solution would be to add a column called ANNUAL_SALARY, and then UPDATE the annual_salary column to equal SALARY multiplied by 12.
SQL> ALTER TABLE employees
2 ADD ANNUAL_SALARY NUMBER(9,2);
Table altered.
SQL> UPDATE employees
2 SET annual_salary = salary*12;
3 rows updated.
SQL> SELECT * FROM employees;
EMPLOYEE_ID FIRST_NAME SALARY DEPARTMENT_ID ANNUAL_SALARY
----------- ----------- ------ ------------- -------------
10 Mike 4000 100 48000
11 James 3500 101 42000
12 David 2000 100 24000This also works fine, but what if David’s salary changes to 3000.
SQL> UPDATE employees
2 SET salary = 3000
3 WHERE first_name = 'David';
1 row updated.
SQL> SELECT * FROM employees;
EMPLOYEE_ID FIRST_NAME SALARY DEPARTMENT_ID ANNUAL_SALARY
----------- ----------- ------ ------------- -------------
10 Mike 4000 100 48000
11 James 3500 101 42000
12 David 3000 100 24000Notice that David’s salary changed to 3000, but his annual salary is now wrong. It would have to be updated to be correct.
An easy way around this problem is to create a virtual column that stores the expression salary*12. This will allow the ANNUAL_SALARY column to be computer at run time and be based on the current data from the SALARY column.
SQL> ALTER TABLE employees
2 DROP COLUMN annual_salary;
Table altered.
SQL> ALTER TABLE employees
2 ADD annual_salary GENERATED ALWAYS AS (salary*12) VIRTUAL;
Table altered.
SQL> SELECT * FROM employees;
EMPLOYEE_ID FIRST_NAME SALARY DEPARTMENT_ID ANNUAL_SALARY
----------- ----------- ------ ------------- -------------
10 Mike 4000 100 48000
11 James 3500 101 42000
12 David 3000 100 36000Now, the ANNUAL_SALARY column is computed at run time with the current column values. If David’s salary is changed again, the ANNUAL_SALARY column will be computed with his new salary and display the correct value.
SQL> UPDATE employees
2 SET salary = 2500
3 WHERE first_name = 'David';
1 row updated.
SQL> SELECT * FROM employees;
EMPLOYEE_ID FIRST_NAME SALARY DEPARTMENT_ID ANNUAL_SALARY
----------- ----------- ------ ------------- -------------
10 Mike 4000 100 48000
11 James 3500 101 42000
12 David 2500 100 30000The use of the virtual column allows for the ANNUAL_SALARY column to always be up-to-date with the current salary of the employees. It also saves time from having to write the expression in the SELECT statement.
Virtual Column Limitations
- Virtual columns cannot reference other virtual columns
- DML cannot be used on virtual columns
- Virtual columns can only reference columns that are in the same table as the virtual column
- Data cannot be inserted into a virtual column since the virtual column does not store data
Virtual Column Benefits
- Commonly used expressions can be stored in a virtual column to save time from having to write the expression and give an alias to the expression in a SELECT statement.
- Virtual columns are computed at run time, so the data is based off the current column values
Link to script that contains the examples in this post.