New Features of Oracle 11g (1)-virtual column, oracle11g

New Features of Oracle 11g (1)-virtual column, oracle11g

Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit

The Oracle11g introduces new features of virtual columns, which are described as follows:

1> You can only create virtual columns on the heap organization table (normal table). You cannot create virtual columns on the index organization table, External table, or temporary table.

2> the virtual Column cannot be of the LOB or RAW type.

3> the virtual column value does not actually exist. The virtual column value is calculated based on the expression only when it is used. It is not stored on the disk.

4> you can use virtual columns as partition keywords to create a partition table. This is another new feature of ORACLE 11g-virtual column partitioning.

5> you can create an index on a virtual column.

6> If a virtual column is added to a created table, if the field type of the virtual column is not specified, ORACLE automatically sets the field type based on the calculation results of the expressions following generated always.

7> the virtual column value is automatically calculated by ORACLE based on the expression. The UPDATE and INSERT operations cannot be performed, and the virtual column can be deleted.

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

9> expressions cannot use other virtual Columns

-- Create a table with virtual Columns

SQL> create table test (n1 number, c1 varchar2 (80), n2 number generated always as (n1 * 0.8); -- create a partition 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 index SQL> create index inx_test on test1 (v1 );