How to Create Virtual Columns with Oracle 11g New Feature?

Oracle 11g supports virtual columns which are derived from the other columns in the table. You can use the virtual columns in your queries, you can index them, you can collect the statistics, etc.
There are few restrictions on the virtual columns.
You cannot write/insert the data into virtual columns.
There is no support for index_organized, external, object, cluster, temporary tables.
There is no support for Oracle-supplied datatypes, user-defined datatypes, LOBs, or LONG RAWs.

See example Below.
SQL> desc emp;
 Name                                      Null?    Type
 ------------------------------------------ -------- --------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2) 
Adding Virtual column to the table

SQL>  alter table emp add annual_sal as ((SAL+nvl(COMM,0))*12) ;
Table altered.
SQL> desc emp
 Name                                       Null?    Type
 ------------------------------------------- -------- --------------
 EMPNO                                      NOT NULL NUMBER(4)
 ENAME                                               VARCHAR2(10)
 JOB                                                 VARCHAR2(9)
 MGR                                                 NUMBER(4)
 HIREDATE                                            DATE
 SAL                                                 NUMBER(7,2)
 COMM                                                NUMBER(7,2)
 DEPTNO                                              NUMBER(2)
 ANNUAL_SAL                                          NUMBER
SQL> select empno, ename, sal, comm, annual_sal from emp;
     EMPNO ENAME             SAL       COMM ANNUAL_SAL
---------- ---------- ---------- ---------- ----------
      7369 SMITH             800                  9600
      7499 ALLEN            1600        300      22800
      7521 WARD             1250        500      21000
      7566 JONES            2975                 35700
      7654 MARTIN           1250       1400      31800
      7698 BLAKE            2850                 34200
      7782 CLARK            2450                 29400
      7788 SCOTT            3000                 36000
      7839 KING             5000                 60000
      7844 TURNER           1500          0      18000
      7876 ADAMS            1100                 13200
      7900 JAMES             950                 11400
      7902 FORD             3000                 36000
      7934 MILLER           1300                 15600
14 rows selected.


How to add Function based Virtual columns to the table?


SQL> alter table emp add Month_of_Join as (to_char(HIREDATE,'Mon'));
Table altered.
SQL> select * from emp;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO ANNUAL_SAL MON
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- ---
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20       9600 Dec
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30      22800 Feb
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30      21000 Feb
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20      35700 Apr
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30      31800 Sep
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30      34200 May
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10      29400 Jun
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20      36000 Dec
      7839 KING       PRESIDENT            17-NOV-81       5000                    10      60000 Nov
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30      18000 Sep
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20      13200 Jan
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30      11400 Dec
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20      36000 Dec
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10      15600 Jan
14 rows selected.

Index Creation on Virtual Columns:
SQL> create index emp_ann_sal on emp(ANNUAL_SAL);
Index created.
SQL>  SELECT index_name,
        index_type,
        funcidx_status
 FROM   user_indexes
 WHERE  table_name = 'EMP';

INDEX_NAME                     INDEX_TYPE                  FUNCIDX_
------------------------------ --------------------------- --------
EMP_ANN_SAL                    FUNCTION-BASED NORMAL       ENABLED
How to see the column expressions

SQL> 
  1  SELECT *
  2  FROM   user_ind_expressions
  3* WHERE  table_name = 'EMP'
SQL> /
INDEX_NAME                     TABLE_NAME                     COLUMN_EXPRESSION                        COLUMN_POSITION
------------------------------ ------------------------------ ---------------------------------------- ---------------
EMP_ANN_SAL                    EMP                            ("SAL"+NVL("COMM",0))*12                               1

You cannot insert the values to the Virtual Columns. You will get Ora-54013 error message.

SQL> insert into emp (empno, ANNUAL_SAL) values ('Dave','20000');
insert into emp (empno, ANNUAL_SAL) values ('Dave','20000')
            *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

You can use the below mentioned syntax to create the virtual columns in create table and alter table statements.
column [datatype] [GENERATED ALWAYS] AS ( <column_expression> ) [VIRTUAL] [( inline_constraint [,...] )]
Note the following:
• GENERATED ALWAYS and VIRTUAL are optional and are just used to clarify that the data is not stored on disk.
• COLUMN_EXPRESSION defines the content of the virtual column. It has the following restrictions:
The expression cannot reference another virtual column.
All columns referenced in the expression must exist in the same table.
The output of the column expression must be a scalar value.
Benefits of Virtual Columns
Automatic re-computation of derived columns for ad-hoc query tools
Reduction in redundant disk space for columns that must be derived from other columns (e.g. a MONTH column that is derived from another DATE column).
Easier for interval partitioning

How to add comments for the Virtual Columns?

SQL> comment on column emp.annual_sal is 'Annual salary for the employee for One Year';
Comment created.
SQL>  select column_name, comments from user_col_comments where table_name ='EMP';
COLUMN_NAME                    COMMENTS
------------------------------ ---------------------------------------------
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
ANNUAL_SAL                     Annual salary for the employee for One Year


DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google