Oracle DB 11g New Feature ( Virtual Columns )

SA,
Today I will explain the oracle 11g new feature "Virtual Columns"

Introduction
 
In the old version in oracle when we want to use expressions and computations we create database views and If we want create index for that expression we create Function-Based Indexes.
Now oracle db 11g allows us to store that expressions in the tables themselves as virtual columns.



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
);

We can see that the virtual column is generated from a simple expression including the other columns in our table. Note that the VIRTUAL keyword is optional.
Virtual column values are not stored on disk. They are generated at runtime using their associated expression.

we can't insert data into  virtual columns, as we can see below.

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 virtual columns, so we will attempt an insert into the physical columns only, as follows.

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

This means, therefore, that we must explicitly reference the physical columns in our 

insert statements, as follows :-

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

we can query our virtual column now as

SQL> select * from emp;

EMPNO    SAL        COMM       SAl_PACK

-----   ------      -----      --------

10      1500        500        2000

1 row selected.

Our expression is evaluated at runtime and gives the output we see above.

Indexes And Constraints At Virtual Columns

Virtual columns are valid for indexes and constraints.
as we can create index on SAL_PACK virtual column as the following

SQL> create index sal_pack_idx on emp(sal_pack);

           Index Created.
Also we can create foreign key in another table that reference sal_pack virtual column.


Using PLSQL Function with virtual columns

We can use PLSQL functions in expression of virtual columns but that require that function must be deterministic as we in next example
  

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

Now let's add virtual column that its expression invlove "sum_num" function

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

Table Altered

Comments on Virtual Columns

we can create comments on virtual columns in the same way as physical columns

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

Comment created.

My opinion is that feature save a lot of time in using expression in base table itself  other than creating views.

I hope that post is helpful and useful.
Mahmoud Ahmed El-Sayed