14 July, 2011

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

7 comments:

  1. Yes, really a good feature.

    Thanks.

    ReplyDelete
  2. We have sell some products of different custom boxes.it is very useful and very low price please visits this site thanks and please share this post with your friends. blackmart apk ios download _ APKForAll

    ReplyDelete
  3. Very efficiently written information. It will be beneficial to anybody who utilizes it, including me. Keep up the good work. For sure i will check out more posts. This site seems to get a good amount of visitors. un curso de milagros

    ReplyDelete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...