江枫渔火愁对面

Oracle11g引入了一个新的特性：虚拟列(Virtual Column)。虚拟列不进行实际的存储，而是通过函数计算得到。目前版本只能在普通的堆表中使用虚拟列，其他如index-organized table, external, object, cluster, 或者temporary table中都还不支持使用虚拟列。

SQL> create table test(i1 int, i2 int,i3 as (i1+i2) virtual);

Table created.

其中，virtual关键字可以省略。这样，i3列就是一个基于i1列和i2列计算得到的一个虚拟列。

NING@11g> insert into test values(1,2,3);
insert into test values(1,2,3)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

NING@11g> insert into test(i1,i2) values(1,2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

I1 I2 I3
———- ———- ———-
1 2 3

Dump出数据block，可以发现数据block中并没有实际的存储i3列的值

NING@11g> select dbms_rowid.rowid_relative_fno(rowid) fno,
2 dbms_rowid.rowid_block_number(rowid) block#
3 from test;

FNO BLOCK#
———- ———-
4 10911

NING@11g> alter system dump datafile 4 block 10911;

System altered.

block_row_dump:
tab 0, row 0, @0x1f8f
tl: 9 fb: –H-FL– lb: 0×1 cc: 2
col 0: [ 2] c1 02
col 1: [ 2] c1 03
end_of_block_dump

构造虚拟列的函数可以基于实际列，也可以基于一些常数，但是不能基于另外一个虚拟列

NING@11g> create table test2(i1 int, i2 as (i+10),i3 as (i2+10));
create table test2(i1 int, i2 as (i+10),i3 as (i2+10))
*
ERROR at line 1:
ORA-54012: virtual column is referenced in a column expression

虚拟列可以基于带deterministic关键字的PL/SQL Function

SQL> create or replace function f_virtual
2 return number deterministic
3 as
4 l_value number;
5 begin
6 l_value :=100;
7 return l_value;
8 end;
9 /

Function created.

SQL> create table test_fun(i1 int,i2 as (f_virtual+1));

Table created.

虚拟列上可以创建索引，创建的索引实际上是一种函数索引

NING@11g> create index ix_test_i3 on test(i3);

Index created.

NING@11g> select FUNCIDX_STATUS from dba_indexes
2 where table_name=’TEST’ and owner=user;

FUNCIDX_STATUS
——–
ENABLED

可以在虚拟列上建约束，比如使用虚拟列作为主键

SQL> alter table test add primary key(i3);

Table altered.

SQL> insert into test(i1,i2) values(2,1);
insert into test(i1,i2) values(2,1)
*
ERROR at line 1:
ORA-00001: unique constraint (NING.SYS_C003582) violated

并且，虚拟列还可以作为分区表的分区键

NING@11g>create table test_part(i1 int, i2 as (i1+100))
2 partition by range(i2)
3 (partition part1 values less than(100),
4 partition part2 values less than(200),
5 partition part3 values less than(maxvalue));

Table created.