New Feature of Oracle11g -- virtual Column)

New Feature of Oracle11g -- VirtualColumn is introduced in the old Oracle version. When we need to use 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. From Oracle

The new feature of Oracle 11g-Virtual Column (Virtual Column) the Virtual Column of Oracle 11G is introduced in the old Oracle version. When we need to use 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. From Oracle

New Features of Oracle 11g -- Virtual Column)

Introduction to Oracle 11G Virtual Column

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.

From the official Oracle documents, we can find the following description of the virtual column technology.

"Tables can also include virtual columns. A virtual column is like any other table column, comment t that its value is derived by evaluating an expression. the expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. you cannot explicitly write to a virtual column."

Oracle 11G introduces virtual columns in the table. A virtual column is an expression that is calculated during runtime. It is not stored in the database and cannot be used to update the values of virtual columns.

Syntax for defining a virtual column:

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151213/0K11C244-0.jpg "style =" float: none; "title =" 121.png" alt = "wKiom1RlyOTAoxtEAADQ7jkzJdE787.jpg"/>

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151213/0K1162T9-1.jpg "style =" float: none; "title =" 122.png" alt = "wKioL1RlyVTyH5csAADgQp0xZ78505.jpg"/>

Column_name [datatype] [generated always] AS [expression] [VIRTUAL]

1. Virtual columns can be used in the where condition of select, update, and delete statements, but cannot be used in DML statements.

2. partitions can be made based on virtual columns.

3. You can create indexes on virtual columns. oracle function indexes are similar.

4. constraints can be created on virtual Columns

Case:

1. Create a table with virtual columns:

14:51:28 SCOTT@ test1 >CREATE TABLE EMP314:51:51   2  (14:51:51   3    EMPNO     NUMBER(6),14:51:51   4    SAL       NUMBER(8,2),14:51:51   5    COMM      NUMBER(8,2),14:51:51   6    SAL_PACK  GENERATED ALWAYS AS ( SAL + NVL(COMM,0) ) VIRTUAL14:51:51   7  )Table created.

2. View virtual column attributes

14:56:10 SCOTT@ test1 >COL TABLE_NAME FOR A1014:56:19 SCOTT@ test1 >COL COLUMN_NAME FOR A2014:56:27 SCOTT@ test1 >COL DATA_TYPE FOR A2014:56:34 SCOTT@ test1 >COL DATA_DEFAULT FOR A2014:56:48 SCOTT@ test1 >R  1  select table_name,COLUMN_NAME,data_type,data_default,VIRTUAL_COLUMN from user_tab_cols  2*  where table_name='EMP3'TABLE_NAME COLUMN_NAME          DATA_TYPE            DATA_DEFAULT         VIR---------- -------------------- -------------------- -------------------- ---EMP3       SAL_PACK             NUMBER               "SAL"+NVL("COMM",0)  YESEMP3       COMM                 NUMBER                                    NOEMP3       SAL                  NUMBER                                    NOEMP3       EMPNO                NUMBER                                    NO

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.

Add virtual columns to a Table:

15:44:12 SCOTT@ test1 >alter table emp3 add (sal_total as (sal*12+comm) virtual);Table altered.15:49:11 SCOTT@ test1 >desc emp3; Name                                                              Null?    Type ----------------------------------------------------------------- -------- -------------------------------------------- EMPNO                                                                      NUMBER(6) SAL                                                                        NUMBER(8,2) COMM                                                                       NUMBER(8,2) SAL_PACK                                                          NOT NULL NUMBER SAL_TOTAL                                                                  NUMBER 15:49:16 SCOTT@ test1 >select * from emp3;     EMPNO        SAL       COMM   SAL_PACK  SAL_TOTAL---------- ---------- ---------- ---------- ----------        10       1500        500       2000      18500        20       3000        500       3500      36500        30       4000        500       4500      48500        40       6000        500       6500      72500        15:51:00 SCOTT@ test1 >select table_name,COLUMN_NAME,data_type,data_default,VIRTUAL_COLUMN from user_tab_cols15:51:27   2  where table_name='EMP3';TABLE_NAME COLUMN_NAME          DATA_TYPE            DATA_DEFAULT         VIR---------- -------------------- -------------------- -------------------- ---EMP3       SAL_TOTAL            NUMBER               "SAL"*12+"COMM"      YESEMP3       SAL_PACK             NUMBER               "SAL"+NVL("COMM",0)  YESEMP3       COMM                 NUMBER                                    NOEMP3       SAL                  NUMBER                                    NOEMP3       EMPNO                NUMBER                                    NO

Use functions in virtual columns:

15:51:37 SCOTT@ test1 >CREATE OR REPLACE FUNCTION sum_sal (in_num1 NUMBER, in_num2 NUMBER)15:57:17   2     RETURN NUMBER DETERMINISTIC15:57:17   3  AS15:57:17   4  BEGIN15:57:17   5     RETURN in_num1 + in_num2;15:57:18   6  END;15:57:19   7  /Function created.15:57:21 SCOTT@ test1 >alter table emp3 add ( sal_comm as (sum_sal(sal,comm)) virtual);Table altered.16:00:03 SCOTT@ test1 >desc emp3 Name                                                              Null?    Type ----------------------------------------------------------------- -------- -------------------------------------------- EMPNO                                                                      NUMBER(6) SAL                                                                        NUMBER(8,2) COMM                                                                       NUMBER(8,2) SAL_PACK                                                          NOT NULL NUMBER SAL_TOTAL                                                                  NUMBER SAL_COMM                                                                   NUMBER 16:00:07 SCOTT@ test1 >select * from emp3;     EMPNO        SAL       COMM   SAL_PACK  SAL_TOTAL   SAL_COMM---------- ---------- ---------- ---------- ---------- ----------        10       1500        500       2000      18500       2000        20       3000        500       3500      36500       3500        30       4000        500       4500      48500       4500        40       6000        500       6500      72500       6500

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

3. Operations on virtual Columns

Insert operation:

We cannot insert data into a virtual column: 15: 01: 52 SCOTT @ test1> insert into emp3 values (,); insert into emp3 values) * ERROR at line 1: ORA-54013: INSERT operation disallowed on virtual columns cannot implicitly add data to virtual columns either: 15: 02: 16 SCOTT @ test1> insert into emp3 values, 500); insert into emp3 values (500,) * ERROR at line 1: ORA-00947: the data of the not enough values virtual column is automatically calculated and generated at 15:07:16 SCOTT @ test1> insert into emp3 (empno, sal, comm) values (500,); 1 row created.15: 07: 29 SCOTT @ test1> select * from emp3; empno sal comm SAL_PACK ---------- 10 1500 500

You cannot update a virtual column:

15:18:45 SCOTT@ test1 >update emp3 set sal_pack=3000;update emp3 set sal_pack=3000       *ERROR at line 1:ORA-54017: UPDATE operation disallowed on virtual columns

Create indexes and constraints on virtual columns:

15:19:07 SCOTT@ test1 >create index emp3_val_ind on emp3(sal_pack) tablespace indx;Index created.15:21:20 SCOTT@ test1 >select table_name,index_name,INDEX_TYPE from user_indexes15:22:11   2   where table_name='EMP3';TABLE_NAME INDEX_NAME                     INDEX_TYPE---------- ------------------------------ ---------------------------EMP3       EMP3_VAL_IND                   FUNCTION-BASED NORMAL15:22:18 SCOTT@ test1 >drop index EMP3_VAL_IND;Index dropped.15:24:37 SCOTT@ test1 >alter table emp3 add constraint pk_emp3 primary key (sal_pack);Table altered.15:25:22 SCOTT@ test1 >select table_name,index_name,INDEX_TYPE from user_indexes15:25:34   2   where table_name='EMP3';TABLE_NAME INDEX_NAME                     INDEX_TYPE---------- ------------------------------ ---------------------------EMP3       PK_EMP3                        FUNCTION-BASED NORMAL

Create a partition table on the virtual column:

15:41:43 SCOTT @ test1> create table EMP3_part15: 41: 46 2 (15:41:46 3 empno number (6), 15:41:46 4 sal number (), 15:41:46 5 comm number ), 15:41:46 6 SAL_PACK generated always as (SAL + NVL (COMM, 0) VIRTUAL15: 41: 46 7) 15:41:46 8 partition by range (sal_pack) 15:41:46 9 (PARTITION sal_2000 values less than (2000), 15:41:46 10 PARTITION sal_4000 values less than (4000), 15:41:46 11 PARTITION sal_6000 Values less than (6000), 15:41:46 12 PARTITION sal_8000 values less than (8000), 15:41:46 13 PARTITION sal_default values less than (MAXVALUE); Table created.15: 42: 33 SCOTT @ test1> insert into emp3_part (empno, sal, comm) select empno, sal, comm from emp3; 4 rows created.15: 43: 33 SCOTT @ test1> commit; Commit complete.15: 43: 36 SCOTT @ test1> select * from emp3_part; empno sal comm SAL_PACK ------------------ -- ---------- 10 1500 500 2000 20 3000 500 3500 30 4000 500 4500 6000 500 650015: 43: 44 SCOTT @ test1> select * from emp3_part partition (sal_2000); no rows selected15: 44: 01 SCOTT @ test1> select * from emp3_part partition (sal_4000 ); empno sal comm SAL_PACK ---------- 10 1500 500 2000 20 3000 500 3500 -- the preceding features on virtual columns show that Oracle uses virtual columns to consume CPU computing time, this saves storage space on disks.