New Features of Oracle 11g: Virtual Columns

Today, I will explain the new features of Oracle 11g Database-virtual Columns

Introduction

In the old Oracle version, when we need expressions or some calculation formulas, we will create a database view. If we need to use indexes on this view, we will create a function-based index.

Oracle 11g now allows us to store expressions directly on tables using virtual columns.

Let's look at a simple example:

 
 

  
  
CREATE TABLE EMP  
  
  
(  
  
  
  EMPNO     NUMBER(6),  
  
  
  SAL       NUMBER(8,2),  
  
  
  COMM      NUMBER(8,2),  
  
  
  SAL_PACK  GENERATED ALWAYS AS ( SAL + NVL(COMM,0) ) VIRTUAL  
  
  
); 
 
 

The preceding VIRTUAL column SAL_PACK is created by a simple expression. The keywords used include VIRTUAL, but this keyword is optional ), the value of this field is calculated by the COMM field through the expression.

Virtual column values are not stored on disks. They are calculated temporarily based on the defined expression during query.

We cannot insert data into a virtual column:

 
 

  
  
SQL> INSERT INTO emp VALUES (10, 1500, 500,2000);  
  
  
 
  
  
ERROR at line 1:  
  
  
ORA-54013: INSERT operation disallowed on virtual columns 
 
 

We cannot implicitly add data to a virtual column either:

 
 

  
  
SQL> INSERT INTO t VALUES (10, 1500, 500);  
  
  
            *  
  
  
ERROR at line 1:  
  
  
ORA-00947: not enough values  
 
 

We can only use physical columns to insert data.

 
 

  
  
SQL> INSERT INTO t (empno, sal,comm) VALUES (10, 1500 , 500);  
  
  
1 row created. 
 
 

You can then query the values of virtual columns:

 
 

  
  
SQL> select * from emp;  
  
  
EMPNO    SAL        COMM       SAl_PACK  
  
  
-----   ------      -----      --------  
  
  
10      1500        500        2000  
  
  
 
  
  
1 row selected. 
 
 

The expression is calculated in real time during query, and the above results are output.

Indexes and constraints of virtual Columns

Indexes and constraints can also be applied to virtual columns. You can use the following SQL statement to create an index:

 
 

  
  
SQL> create index sal_pack_idx on emp(sal_pack);  
  
  
           Index Created. 
 
 

You can also create a foreign key for a virtual column.

Use PLSQL functions to process virtual Columns

The PLSQL function can be used to define virtual columns, but the function must be determined:

 
 

  
  
CREATE OR REPLACE FUNCTION sum_num (in_num1 NUMBER, in_num2 NUMBER)  
  
  
   RETURN NUMBER DETERMINISTIC  
  
  
AS 
  
  
BEGIN 
  
  
   RETURN in_num1 + in_num2;  
  
  
END; 
 
 

You can then use the above functions in the virtual column:

 
 

  
  
SQL>ALTER TABLE emp ADD sal_pack_temp GENERATED ALWAYS AS ( sum_num(SAL,COMM) ):  
  
  
Table Altered 
 
 

Comments of virtual Columns

To create a comment for a virtual column:

 
 

  
  
SQL> COMMENT ON COLUMN emp.sal_pack IS 'Virtual column [sal+ comm]';  
  
  
Comment created. 
 
 

In the above example, the virtual column function is much simpler than the view itself.

I hope this will also work for you.