Dec 14 2007

Oracle 11g new feature - Virtual Column

Published by kamus at 10:47 pm under Oracle RDBMS

在之前的一篇 - Oracle 11g New Feature - Partition 文章中曾经提到虚拟列的概念，但是当时自己也有些疑问，今天在Oracle 11.1.0.6 上简单测试了一下。

  1. CREATE TABLE tb_v
  2. (col_1 number(6) not null,
  3. col_2 number not null,
  4. col_v as (col_1+col_2));
  5.  
  6. -- 由于虚拟列的存在，所以即使指定了全部的实际列的值也会报值不足的错误
  7. SQL> insert into tb_v values(1,2);
  8.  
  9. insert into tb_v values(1,2)
  10.  
  11. ORA-00947: not enough values
  12.  
  13. -- 虚拟列中不允许显示插入值
  14. SQL> insert into tb_v values(1,2,4);
  15.  
  16. insert into tb_v values(1,2,4)
  17.  
  18. ORA-54013: INSERT operation disallowed on virtual columns
  19.  
  20. --必须明确指定列名，才能正常插入数据
  21. SQL> insert into tb_v(col_1,col_2) values(1,2);
  22.  
  23. 1 row inserted
  24.  
  25. --检索表，已经自动计算虚拟列的值
  26. SQL> select * from tb_v;
  27.  
  28.   COL_1      COL_2      COL_V
  29. ------- ---------- ----------
  30.       1          2          3
  31.  
  32. --选取该行ROWID
  33. SQL> select rowid from tb_v;
  34.  
  35. ROWID
  36. ------------------
  37. AAAEdcAAEAAAC3/AAA
  38.  
  39. -- 获得该行数据存放得到数据文件号
  40. SQL> select dbms_rowid.rowid_relative_fno(row_id => 'AAAEdcAAEAAAC3/AAA') from dual;
  41.  
  42. DBMS_ROWID.ROWID_RELATIVE_FNO(
  43. ------------------------------
  44.                              4
  45.  
  46. --获得该行数据存放的block号                            
  47. SQL> select dbms_rowid.rowid_block_number(row_id => 'AAAEdcAAEAAAC3/AAA') from dual;
  48.  
  49. DBMS_ROWID.ROWID_BLOCK_NUMBER(
  50. ------------------------------
  51.                          11775
  52.  
  53. -- Dump这个数据块
  54. SQL> alter system dump datafile 4 block 11775;
  55.  
  56. System altered

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

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

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

  1. SQL> create index idx_v on tb_v (col_v);
  2.  
  3. Index created
  4.  
  5. -- 获得索引存储的文件号和block号
  6. SQL> exec show_space(p_segname_a => 'idx_v',p_type_a => 'INDEX');
  7.  
  8. Total Blocks............................8
  9. Total Bytes.............................65536
  10. Unused Blocks...........................4
  11. Unused Bytes............................32768
  12. Last Used Ext FileId....................4
  13. Last Used Ext BlockId...................11777
  14. Last Used Blocks........................4
  15. Last Used BlockId.......................11780
  16. FIRST LEVEL BITMAP BLOCK................11777
  17. SECOND LEVEL BITMAP BLOCK...............11778
  18. PAGETABLE SEGMENT HEADER................11779
  19. FIRST Trans Data BLOCK..................11780
  20. Dump SQL: alter system dump datafile 4 block 11780;

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

row#0[8024] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  01 00 2d ff 00 00
----- end of leaf block dump -----

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

其它的一些测试可以参看Oracle11g新特性：虚拟列virtual column from Ningoo。

下一篇计划测试虚拟列的性能。

3 Responses to “Oracle 11g new feature - Virtual Column”

  1. kamus Says:
    December 15th, 2007 at 2:10 am [ Quote ]

    测试coComment

  2. Laurent Schneider Says:
    December 16th, 2007 at 5:55 pm [ Quote ]

    insert into tb_v values(1,2,default);

    Thanks Google for the translation 8-)

  3. kamus Says:
    December 16th, 2007 at 11:38 pm [ Quote ]

    @Laurent
    Thanks for your update and Google is god :D
    BTW:
    Your site is blocked by China GFW, I can only visit it in my office.

Trackback URI | Comments RSS


Leave a Reply

-> :( :! :D :| :? :X ;;) :) ;)