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.
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
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.
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, @0×1f8f
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
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.
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
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
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.
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.
基于视图？
什么基于视图？
这个虚拟列的内部实现是通过视图么？
内部通过函数，存放与数据字典中，你要理解成视图也无不可，但是和你实际创建的视图肯定是有区别的
了解