Shulou(Shulou.com)06/01 Report--

Virtual column (Virtual Column), one of the new features of Oracle 11g

Introduction to Oracle 11G Virtual column Virtual Column

In the old version of Oracle, when we needed to use expressions or some calculation formulas, we created database views, and if we needed to use indexes on this view, we created function-based indexes.

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

"Tables can also include virtual columns. A virtual column is like any other table column, except 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 into tables. Virtual columns are expressions that are calculated at run time, are not stored in the database, and cannot update the values of virtual columns.

Define the syntax for a virtual column:

Column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]

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

two。 Partitioning can be based on virtual columns

3. Indexes can be built on virtual columns, as is the case with oracle's functional indexes.

4. You can create constraints on virtual columns

Case study:

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 SAL NUMBER), 14:51:51 5 COMM NUMBER (8 COMM,0 2), 14:51:51 6 SAL_PACK GENERATED ALWAYS AS (SAL + NVL (COMM,0)) VIRTUAL14:51:51 7) Table created.

2. View virtual column properties

14:56:10 SCOTT@ test1 > COL TABLE_NAME FOR A1014 COL TABLE_NAME FOR 56 SCOTT@ test1 19 SCOTT@ test1 > COL COLUMN_NAME FOR A2014 56 COL COLUMN_NAME FOR 27 SCOTT@ test1 > COL DATA_TYPE FOR A2014 56 COL COLUMN_NAME FOR 34 SCOTT@ test1 > COL DATA_DEFAULT FOR A2014 56 Swiss 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 virtual column SAL_PACK created above is created by a simple expression, using the keyword VIRTUAL (although this keyword is optional), and the value of this field is calculated by the expression of the field COMM.

Add a virtual column on the 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 (8pm 2) COMM NUMBER (8pm 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 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 (8pm 2) COMM NUMBER (8pm 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 2000 18500 2000 20 3000 500 3500 36500 3500 30 4000 500 4500 48500 4500 40 6000 500 6500 72500 6500

The values of the virtual columns are not stored on disk; they are temporarily calculated based on the defined expression during the 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 a virtual column: 15:02:16 SCOTT@ test1 > insert into emp3 values (10meme 1500500) Insert into emp3 values (10Magazine 1500500) * ERROR at line 1:ORA-00947: the data of the not enough values virtual column is automatically calculated to generate 15:07:16 SCOTT@ test1 > insert into emp3 (empno,sal,comm) values (10Magazine 1500500); 1 row created.15:07:29 SCOTT@ test1 > select * from emp3 EMPNO SAL COMM SAL_PACK- 10 1500 500 2000

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 (8 EMPNO NUMBER 2), 15:41:46 5 COMM NUMBER (8 SCOTT@ test1 2), 15:41:46 6 SAL_PACK GENERATED ALWAYS AS (SAL + NVL (COMM) ) 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 40 6000 500 650015 from emp3_part partition 43 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 2000 20 3000 500 3500-- from the above characteristics of virtual columns, we can see that the adoption of virtual columns in Oracle takes up CPU computing time. The storage space of the disk is saved.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.