Oracle 11g introduced the concept of ‘Virtual Column’ within a table. Virtual Columns are similar to normal table’s columns but with the following differences:
They are defined by an expression. The result of evaluation of this expression becomes the value of the column. The values of the virtual column are not stored in the database. Rather, it’s computed at run-time when you query the data. You can’t update (in SET clause of update statement) the values of virtual column. These are read only values, that are computed dynamically and any attempt to modify them will result into oracle error.
The syntax for defining a virtual column is:
column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]
where the parameters within [ ] are optional and can be omitted. If you don’t mention the datatype, Oracle will decide it based on the result of the expression.
Excepting the above points, a virtual column, exists just like any other column of a normal table and the following points apply to it:
Virtual columns can be used in the WHERE clause of UPDATE and DELETE statement but they can’t be modified by DML.
Statistics can be collected on them.
They can be used as a partition key in virtual column based partitioning.
Indexes can be created on them. As you might have guessed, oracle would create
- Function based indexes as we create on normal tables.
- Constraints can be created on them.
For creating a virtual column, use the syntax mentioned above. Consider the following example:
CREATE TABLE VTEST
(
emp_id NUMBER,
emp_no VARCHAR2(50),
monthly_sal NUMBER(10,2),
bonus NUMBER(10,2),
tot_sal NUMBER(10,2) GENERATED ALWAYS AS (monthly_sal*10 + 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 = 'VTEST';
COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_DEFAULT | VIRTUAL_COLUMN
EMP_ID | NUMBER | 22 | null | NO
EMP_NO | VARCHAR2 | 50 | null | NO
MONTHLY_SAL | NUMBER | 22 | null | NO
BONUS | NUMBER | 22 | null | NO
TOT_SAL | NUMBER | 22 | "MONTHLY_SAL"*12+"BONUS" | YES
Lets test this :
CREATE TABLE VTEST
(
emp_id NUMBER,
emp_no VARCHAR2(50),
monthly_sal NUMBER(10,2),
bonus NUMBER(10,2),
tot_sal NUMBER(10,2) GENERATED ALWAYS AS (monthly_sal*10 + bonus)
);
Table created.
when you insert data into table you only insert for non virtual columns. See below
SQL> INSERT INTO VTEST (emp_id, emp_no, monthly_sal, bonus) values (1,'arvind',10000,1000);
1 row created.
SQL> INSERT INTO VTEST (emp_id, emp_no, monthly_sal, bonus) values (2,'reddy',20000,2000);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from vtest;
EMPL_ID EMPL_NM MONTHLY_SAL BONUS TOT_SAL
---------- ---------- ---------- ------------ ----------
1 arvind 10000 1000 121000
2 reddy 20000 2000 242000
