Oracle 11g had brought multiple language enhancement features. These features have not only added the language utility, but also widened the programming scope of database developers.
In this tutorial, we shall discuss one of those utility features – Virtual Columns. Virtual columns are the columns, which derive their value during run-time through an expression. We shall discuss a lot more in detail.
Virtual Columns: History
Virtual columns are not new to Oracle. In earlier versions of Oracle, they were auto categorized by Oracle server. This categorization was majorly for maintenance of collection columns in Oracle. Refer the illustration below.
A nested table TYPE_VIRT has been created and a table TAB_VIRT uses it as one of its column’s type. Oracle identifies the actual column as the virtual column
Example [1]
SQL > CREATE TYPE TYPE_VIRT AS TABLE OF NUMBER; / TYPE created. SQL > CREATE TABLE TAB_VIRT (A NUMBER, B TYPE_VIRT) NESTED TABLE B STORE AS NESTVIRT; TABLE created. SQL > SELECT TABLE_NAME, COLUMN_NAME, VIRTUAL_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME=’TAB_VIRT’; TABLE_NAME COLUMN_NAME VIR ----------- ------------- --- TAB_VIRT A NO TAB_VIRT B YES TAB_VIRT SYS_NC0000200003$ NO
Understanding Virtual Columns
The concept of Virtual Columns has been extended in Oracle 11g, so that database users can make use of the feature in tables.
Very often, we come across the requirement where the value of a column has to be manipulated based on other columns. For example, the net commission of an employee can be calculated as SALARY*(COMM/100). As per the earlier conventional solutions, one can create a view where one of the selected attribute is always generated as the expression value. But note that view attributes are not physically stored in the database. Besides storage issues, these view attributes cannot be indexed and cannot enjoy the rights of a table column.
Now, Oracle 11g came up with the creation of manual virtual columns. They are the columns which would be physically stored in the database like other columns. They can be indexed, constrained and even participate in table partitioning. Their value is derived through an expression. The derivative expression must contain the non virtual columns of the same table, or constants, or a deterministic function.
We shall see the syntax and example creation of a virtual column in a table.
Syntax [1]
COLUMN [DATATYPE] GENERATED ALWAYS AS (EXPRESSION) VIRTUAL (CONSTRAINT)
Explanation of Syntax
COLUMN – Virtual Column Name [DATATYPE] – Optional. Except RAW and LOB data types, all data types are accepted. If not specified, the return type of the expression will be data type of the virtual column. GENERATED ALWAYS AS – Optional EXPRESSION – Contains non virtual columns, constants or deterministic functions VIRTUAL – Optional keyword to specify VIRTUAL feature CONSTRAINT – Constraint clause for the column
In the example below, a table T_VIRT is created with two non virtual columns (A and B) and C as virtual column. Note that C’s value is derived as sum of columns A and B.
Example [2]
SQL > CREATE TABLE T_VIRT (A NUMBER, B NUMBER, C NUMBER AS (A+B)); TABLE created.
The column VIRTUAL_COLUMN in USER_TAB_COLS reveals the nature of the column. Note this column is still not available in USER_TAB_COLUMNS.
As stated earlier, value of a virtual column can only be a derivative and cannot be fed. Direct insert into a virtual column raises exception ORA-54013. The value of a virtual column is always generated during runtime.
Example [3]
SQL > INSERT INTO T_VIRT VALUES ( 1,2,3); INSERT INTO T_VIRT * ERROR at line 1: ORA-54013: INSERT operation disallowed ON virtual COLUMNS
All the non virtual columns must be specified in the INSERT statement. If columns specification is ignored, Oracle raises exception ORA-00947 as shown below.
Example [4]
SQL > INSERT INTO T_VIRT VALUES ( 1,2); INSERT INTO T_VIRT *ERROR at line 1:ORA-00947: NOT enough VALUES
The INSERT statement below is the correct way to feed data into table with virtual columns.
Example [5]
SQL > INSERT INTO T_VIRT(A,B) VALUES (10,20);
1 row created.
SQL > SELECT * FROM T_VIRT; A B C ---------- ---------- ---------- 10 20 30
A function can also be used in virtual columns derivative expressions. Only deterministic functions are admissible functions to be used in virtual column expressions. Refer the illustration below for the usage of functions in virtual columns.
F_VIRT is a stored function to add two input parameters. If it gets used in virtual column expression, oracle raises exception ORA-30553 to restrict the table creation.
Example [6]
SQL > CREATE OR REPLACE FUNCTION F_VIRT (P1 NUMBER, P2 NUMBER) RETURN NUMBER IS BEGIN RETURN (P1+P2); END; / FUNCTION created. SQL > CREATE TABLE T_VIRT_DET (A NUMBER, B NUMBER, C NUMBER AS (F_VIRT(A,B))) / C NUMBER AS (F_VIRT(A,B))) * ERROR at line 4: ORA-30553: The FUNCTION IS NOT deterministic
Now, we shall try declaring the function to a Deterministic one and then using in virtual column expression.
Example [7]
SQL > CREATE OR REPLACE FUNCTION F_VIRT (P1 NUMBER, P2 NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN (P1+P2); END; / FUNCTION created. SQL > CREATE TABLE T_VIRT_DET (A NUMBER, B NUMBER, C NUMBER AS (F_VIRT(A,B)) ); TABLE created.
Table is created successfully. Data can be verified by inserting test data and querying in the query.
Example [8]
SQL > INSERT INTO T_VIRT_DET(A,B)VALUES (5,34); 1 ROW created. SQL > SELECT * FROM T_VIRT_DET; A B C ---------- ---------- ---------- 5 34 39
Including deterministic functions in the virtual column expressions is a handy approach to embed logic at table level but in terms of performance, it is less efficient than simple expression specification. This performance penalty is due to the context switching involved from SQL engine to PL/SQL engine.
Oracle 11g Virtual Columns: Featured Activities
Indexing on Virtual Columns
Virtual columns can be indexed like any other non virtual columns. The index created is always a function based index. If the index is B-tree index, it is recognized as FUNCTION-BASED NORMAL. For bitmap indexes, it is recognized as FUNCTION-BASED BITMAP.
Example [9]
SQL > CREATE INDEX IDX_VIRT ON T_VIRT (C); INDEX created.
The index IDX_VIRT will behave as a function based index. Oracle internally indexes the expression associated with the value.
Constraints can be imposed upon Virtual Columns
Similar to normal columns, all types of constraints can be imposed upon virtual columns. The example below uses the virtual column ‘C’ to be the primary key of the table T_VIRT.
Example [10]
SQL > ALTER TABLE T_VIRT ADD PRIMARY KEY (C) TABLE altered. SQL > SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = 'T_VIRT'; CONSTRAINT_NAME TABLE_NAME COLUMN_NAME -------------------- -------------------- -------------------- SYS_C0012705 T_VIRT C
Virtual Column can participate in Table partitioning
Virtual Column based partitioning can be applied with all the partitioning techniques. In the illustration below, a table T_VIRT_PART is created using Interval Partitioning Technique.
Example [11]
CREATE TABLE T_VIRT_PART (A NUMBER, B NUMBER, C AS (A+B)) PARTITION BY RANGE (C) INTERVAL (10000) (PARTITION P0 VALUES LESS THAN (20000), PARTITION P1 VALUES LESS THAN (30000), PARTITION P2 VALUES LESS THAN (40000)) / TABLE created.
Test data is inserted into the partitions.
SQL > INSERT INTO T_VIRT_PART(A,B) VALUES (&N,&M); Enter VALUE FOR n: 3829 Enter VALUE FOR m: 8292 OLD 1: INSERT INTO T_VIRT_PART(A,B) VALUES (&N,&M) NEW 1: INSERT INTO T_VIRT_PART(A,B) VALUES (3829,8292) 1 ROW created. SQL > / Enter VALUE FOR n: 9201 Enter VALUE FOR m: 492 OLD 1: INSERT INTO T_VIRT_PART(A,B) VALUES (&N,&M) NEW 1: INSERT INTO T_VIRT_PART(A,B) VALUES (9201,492) 1 ROW created. SQL > / Enter VALUE FOR n: 1739 Enter VALUE FOR m: 8293 OLD 1: INSERT INTO T_VIRT_PART(A,B) VALUES (&N,&M) NEW 1: INSERT INTO T_VIRT_PART(A,B) VALUES (1739,8293) 1 ROW created. SQL > / Enter VALUE FOR n: 11392 Enter VALUE FOR m: 10982 OLD 1: INSERT INTO T_VIRT_PART(A,B) VALUES (&N,&M) NEW 1: INSERT INTO T_VIRT_PART(A,B) VALUES (11392,10982) 1 ROW created. SQL > / Enter VALUE FOR n: 23992 Enter VALUE FOR m: 11392 OLD 1: INSERT INTO T_VIRT_PART(A,B) VALUES (&N,&M) NEW 1: INSERT INTO T_VIRT_PART(A,B) VALUES (23992,11392) 1 ROW created. SQL > / Enter VALUE FOR n: 33820 Enter VALUE FOR m: 4292 OLD 1: INSERT INTO T_VIRT_PART(A,B) VALUES (&N,&M) NEW 1: INSERT INTO T_VIRT_PART(A,B) VALUES (33820,4292) 1 ROW created. SQL > SELECT * FROM T_VIRT_PART; A B C ---------- ---------- ---------- 3829 8292 12121 9201 492 9693 1739 8293 10032 11392 10982 22374 23992 11392 35384 33820 4292 38112 6 ROWS selected.
Now, we can query the table with their partition names.
SQL > SELECT * FROM T_VIRT_PART PARTITION (P0); A B C ---------- ---------- ---------- 3829 8292 12121 9201 492 9693 1739 8293 10032 SQL > SELECT * FROM T_VIRT_PART PARTITION (P1); A B C ---------- ---------- ---------- 11392 10982 22374 SQL > SELECT * FROM T_VIRT_PART PARTITION (P2); A B C ---------- ---------- ---------- 23992 11392 35384 33820 4292 38112
To demonstrate Interval Partitioning, we shall insert a data whose value falls out of specified partition range (High_Value). In this situation, oracle server implicitly creates a partition suffixed by SYS_.
Example [12]
SQL > INSERT INTO T_VIRT_PART (A,B) VALUES (37282,17282); 1 ROW created. SQL > SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, INTERVAL FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'T_VIRT_PART'; TABLE_NAME PARTITION_NAME HIGH_VALUE INT -------------------- -------------------- -------------------- --- T_VIRT_PART P0 20000 NO T_VIRT_PART P1 30000 NO T_VIRT_PART P2 40000 NO T_VIRT_PART SYS_P41 60000 YES SQL > SELECT * FROM T_VIRT_PART PARTITION (SYS_P41); A B C ---------- ---------- ---------- 37282 17282 54564
Virtual Columns: Advantages
The strongest benefit from virtual columns is that they are physically stored in database but their values do not occupy the disk space. Values are generated at runtime through the derivative expression and still, statistics can be generated for the column.
In addition, virtual column gives benefit of indexing, partitioning and constraint imposition.
