Oracle 11g new Features (i)--Virtual columns

DB version: Oracle database 11g Enterprise Edition release 11.2.0.2.0- 64bit

ORACLE11G adds new features for virtual columns, as described below:

1> can only create virtual columns on the heap organization table (normal table), cannot create virtual columns on index organization tables, external tables, temporary tables

2> Virtual column cannot be lob or raw type

The value of the 3> virtual column is not real and is only used to calculate the value of the virtual column based on the expression, and the disk does not store

4> can create a partitioned table of virtual columns as partition keywords, another new feature of Oracle 11g- virtual column Partitioning

5> can be indexed on a virtual column

6> If you add a virtual column to a table that you have already created, if you do not specify a field type for the virtual column,Oracle automatically sets the type of the field based on the result of the expression that follows generated always as

The value of the 7> virtual column is calculated automatically by the Oracle based on the expression and cannot be done with the update and insert operations, which can be done with a DELETE operation on the virtual column

8> all columns in an expression must be in the same table

9> expression cannot use other virtual columns

--Create a table with a virtual column

Sql> CREATE TABLE Test (N1 number, C1 varchar2, N2 number generated always as (n1*0.8)); --Create a partitioned table with virtual columns

sql> CREATE TABLE Test1 (N1 number,

C1 VARCHAR2 (80),

V1 VARCHAR2 (2) generated always as (substr (c1,1,1)))

partition by List (v1)

(Partition v11 values (' I '),

Partition V12 values (' O '),

Partition V13 values (' E '),

Partition V15 values (default));

Sql> SELECT * from Test1 partition (V11);

--Create an index on the virtual column of the partitioned table sql> Create, Index inx_test on test1 (v1);

Oracle 11g new Features (i)--Virtual columns