快捷搜索:

Oracle 11g新特性:虚拟列 - 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’这个前提获得验证。

您可能还会对下面的文章感兴趣: