advertisements
_____________________________________________________________________________________________________________________
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 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
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment