Oracle 11g新特性：虚拟列

核心提示：今天在Oracle 11.1.0.6 上简单测试了一下， CREATE TABLE tb_v(col_1 number(6) not null,col_2 number not null,col_v as (col_1+col_2));-- 由于虚拟列的存在，Oracle 11g新特性：虚拟列，所以即使指定了全部的实际

今天在Oracle 11.1.0.6 上简单测试了一下。

CREATE TABLE tb_v
(col_1 number(6) not null,
col_2 number not null,
col_v as (col_1+col_2));
　
-- 由于虚拟列的存在，所以即使指定了全部的实际列的值也会报值不足的错误
SQL> insert into tb_v values(1,2);
　
insert into tb_v values(1,2)
　
ORA-00947: not enough values
　
-- 虚拟列中不允许显示插入值
SQL> insert into tb_v values(1,2,4);
　
insert into tb_v values(1,2,4)
　
ORA-54013: INSERT operation disallowed on virtual columns
　
--必须明确指定列名，才能正常插入数据
SQL> insert into tb_v(col_1,col_2) values(1,2);
　
1 row inserted
　
--检索表，已经自动计算虚拟列的值
SQL> select * from tb_v;
　
　COL_1　　　COL_2　　　COL_V
------- ---------- ----------
　　　1　　　　　2　　　　　3
　
--选取该行ROWID
SQL> select rowid from tb_v;
　
ROWID
------------------
AAAEdcAAEAAAC3/AAA
　
-- 获得该行数据存放得到数据文件号
SQL> select dbms_rowid.rowid_relative_fno(row_id => 'AAAEdcAAEAAAC3/AAA') from dual;
　
DBMS_ROWID.ROWID_RELATIVE_FNO(
------------------------------
　　　　　　　　　　　　　　 4
　
--获得该行数据存放的block号　　　　　　　　　　　　　　
SQL> select dbms_rowid.rowid_block_number(row_id => 'AAAEdcAAEAAAC3/AAA') from dual;
　
DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------
　　　　　　　　　　　　 11775
　
-- Dump这个数据块
SQL> alter system dump datafile 4 block 11775;
　
System altered

下面是dump内容的节选，可以看到确实只保存了两个字段，也就是虚拟列的值并没有存储在block中

　　block_row_dump:
　　tab0,row0,@0x1f8f
　　tl:9fb:--H-FL--lb:0x1cc:2
　　col0:[2]c102
　　col1:[2]c103
　　end_of_block_dump

继续在虚拟列上创建索引，然后再看看索引的存储是否有不一样的地方。

SQL> create index idx_v on tb_v (col_v);
　
Index created
　
-- 获得索引存储的文件号和block号
SQL> exec show_space(p_segname_a => 'idx_v',p_type_a => 'INDEX');
　
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................4
Last Used Ext BlockId...................11777
Last Used Blocks........................4
Last Used BlockId.......................11780
FIRST LEVEL BITMAP BLOCK................11777
SECOND LEVEL BITMAP BLOCK...............11778
PAGETABLE SEGMENT HEADER................11779
FIRST Trans Data BLOCK..................11780
Dump SQL: alter system dump datafile 4 block 11780;

Dump 索引块，下面是Dump内容的节选，c1 04就是3，因此可见在创建索引的时候，Oracle会先去计算虚拟列的值，然后再根据结果创建索引。

　　row#0[8024]flag:------,lock:0,len=12
　　col0;len2;(2):c104
　　col1;len6;(6):01002dff0000
　　-----endofleafblockdump-----

实际上基于虚拟列的索引就是一个函数索引，可以从DBA_INDEXES.FUNCIDX_STATUS=’ENABLED’这个条件得到验证。