唯一性索引和非唯一性索引深入

记得在tom的oracle 9i&10g编程艺术中这样一句话：在一个非唯一索引中，oracle会把rowid作为一个额外列加到键上，使索引键为唯一; 先按索引键排序，再按rowid升序排序;在一个唯一索引中，不会再加rowid到索引键上。今天有时间验证一下,呵呵。

验证：非唯一索引，oracle会把rowid作为一个额外列加到键上，使索引键为唯一;先按索引键排序，再按rowid升序排序

SQL> create table test_nounique(a number);

Table created.

SQL> begin

  2  for i in 1..10000 loop   

  3  insert into test_nounique values(i);

  4  end loop;

  5  commit;

  6  end;

  7  /

SQL> begin

  2  for i in 1..4000 loop

  3  insert into test_nounique values(1000);

  4  end loop;

  5  commit;

  6  end;

  7  /

现在有4001个相同值1000

创建非唯一索引

SQL> create index ind_nounique on test_nounique(a);

Index created.

SQL> select object_id from dba_objects where object_name=upper('ind_nounique');

 OBJECT_ID

----------

   32518

dump索引结构

SQL> alter session set events 'immediate trace name TREEDUMP level 32518';

Session altered.

----- begin tree dump

branch: 0x10001aa 167772 (0: nrow: 29, level: 1)

   leaf: 0x10001ab 167773 (-1: nrow: 485 rrow: 485)

   leaf: 0x10001ac 167774 (0: nrow: 479 rrow: 479)

   leaf: 0x10001ad 167775 (1: nrow: 510 rrow: 510)

   leaf: 0x10001ae 167776 (2: nrow: 512 rrow: 512)

   leaf: 0x10001af 167777 (3: nrow: 512 rrow: 512)

   leaf: 0x10001b0 167778 (4: nrow: 512 rrow: 512)

   leaf: 0x10001b1 167779 (5: nrow: 512 rrow: 512)

   leaf: 0x10001b2 16777650 (6: nrow: 512 rrow: 512)

   leaf: 0x10001b3 16777651 (7: nrow: 512 rrow: 512)

   leaf: 0x10001b4 16777652 (8: nrow: 508 rrow: 508)

   leaf: 0x10001b5 16777653 (9: nrow: 479 rrow: 479)

   leaf: 0x10001b6 16777654 (10: nrow: 479 rrow: 479)

   leaf: 0x10001b7 16777655 (11: nrow: 478 rrow: 478)

   leaf: 0x10001b8 16777656 (12: nrow: 479 rrow: 479)

   leaf: 0x10001b9 16777657 (13: nrow: 479 rrow: 479)

   leaf: 0x10001ba 16777658 (14: nrow: 479 rrow: 479)

   leaf: 0x10001bb 16777659 (15: nrow: 479 rrow: 479)

   leaf: 0x10001bc 16777660 (16: nrow: 478 rrow: 478)

   leaf: 0x10001bd 16777661 (17: nrow: 479 rrow: 479)

   leaf: 0x10001be 16777662 (18: nrow: 479 rrow: 479)

   leaf: 0x10001bf 16777663 (19: nrow: 479 rrow: 479)

   leaf: 0x10001c0 167776 (20: nrow: 479 rrow: 479)

   leaf: 0x10001c1 16777665 (21: nrow: 478 rrow: 478)

   leaf: 0x10001c2 16777666 (22: nrow: 479 rrow: 479)

   leaf: 0x10001c3 16777667 (23: nrow: 479 rrow: 479)

   leaf: 0x10001c4 16777668 (24: nrow: 479 rrow: 479)

   leaf: 0x10001c5 16777669 (25: nrow: 478 rrow: 478)

   leaf: 0x10001c6 16777670 (26: nrow: 479 rrow: 479)

   leaf: 0x10001c7 16777671 (27: nrow: 328 rrow: 328)

----- end tree dump

查看分支块(branch block)所在文件及块号

SQL> select dbms_utility.data_block_address_file(167772) "file

  2  dbms_utility.data_block_address_block(167772) "block" from dual;

      file      block

     ----------      ----------

         4        426

SQL> alter system dump datafile 4 block 426

  2  /

System altered.

Branch block dump

=================

header address 183337028=0xaed8044

kdxcolev 1

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 28

kdxcofbo 84=0x54

kdxcofeo 7769=0x1e59

kdxcoavs 7685

kdxbrlmc 167773=0x10001ab  第一个leaf block,下面共列28个叶子块(row#0----row#28)

kdxbrsno 0

kdxbrbksz 8056 

row#0[8047] dba: 167774=0x10001ac

col 0; len 3; (3):  c2 05 57

col 1; TERM

row#1[8038] dba: 167775=0x10001ad

col 0; len 3; (3):  c2 0a 42

col 1; TERM

row#2[8024] dba: 167776=0x10001ae

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a1 02 3e

row#3[8010] dba: 167777=0x10001af

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a2 01 aa

row#4[7996] dba: 167778=0x10001b0

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a3 01 16

row#5[7982] dba: 167779=0x10001b1

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a4 00 82

row#6[7968] dba: 16777650=0x10001b2

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a4 02 82

row#7[7954] dba: 16777651=0x10001b3

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a5 01 ee

row#8[7940] dba: 16777652=0x10001b4

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a6 01 5a

row#9[7931] dba: 16777653=0x10001b5

col 0; len 3; (3):  c2 0b 38

col 1; TERM

row#10[7922] dba: 16777654=0x10001b6

col 0; len 3; (3):  c2 10 23

col 1; TERM

row#11[7913] dba: 16777655=0x10001b7

col 0; len 3; (3):  c2 15 0e

col 1; TERM

row#12[7904] dba: 16777656=0x10001b8

col 0; len 3; (3):  c2 19 5c

col 1; TERM

row#13[75] dba: 16777657=0x10001b9

col 0; len 3; (3):  c2 1e 47

col 1; TERM

---------------------------------省略部分----------------------------------

有没有发现row#2——row#8的col 1都是有值的，其他都是TERM。那为什么被TERM了呢？

如果插入的值不是1000的话（根据这个例子来讲），索引键就可判断插入到那个block中，就不需要rowid来判断。

Col 0的值就是1000，把十六进制转为十进制：

SQL> select uf_dec('&input_hex') from dual;

Enter value for input_hex: c2 0b

old   1: select uf_dec('&input_hex') from dual

new   1: select uf_dec('c2 0b') from dual

UF_DEC('C20B')

--------------

  1000

确认一下：Col1保存的就是rowid的值,rowid是升序排序

SQL> select dbms_utility.data_block_address_file(16777652) "file

  2  dbms_utility.data_block_address_block(16777652) "block" from dual;

row#443[2704] flag: -----, lock: 0

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a7 00 81

row#444[2692] flag: -----, lock: 0

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a7 00 82

row#445[2680] flag: -----, lock: 0

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a7 00 83

row#446[2668] flag: -----, lock: 0

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a7 00 84

row#447[2656] flag: -----, lock: 0

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a7 00 85

row#448[24] flag: -----, lock: 0

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a7 00 86

row#449[2632] flag: -----, lock: 0

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a7 00 87

row#450[2620] flag: -----, lock: 0

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a7 00 88

row#451[2608] flag: -----, lock: 0

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a7 00 

row#452[2596] flag: -----, lock: 0

col 0; len 2; (2):  c2 0b

col 1; len 6; (6):  01 00 01 a7 00 8a

row#453[2584] flag: -----, lock: 0

col 0; len 2; (2):  c2 0b

col 1; len 6; (6): 01 00 01 a7 00 8b

--------------------------------省略部分---------------------------------

SQL> select dump(max(rowid),16) from test_nounique where a=1000;

DUMP(MAX(ROWID),16)

---------------------------------------------------------------

Typ=69 Len=10: 0,0,7f,5,1,0,1,a7,0,8b

发现加粗部分和dump(max(rowid),16)的值是一样的。

可能有人会问：

为什么不select dump(min(rowid),16) from test_nounique where a=1000，再dump最小rowid所在的index block，进行对比？但是dump出来，却找不到这个对应的rowid，是因为最小rowid的1000是在1001之前插入的。奇怪的事：在leaf block中找不到1000的最小rowid？？？

验证：一个唯一索引中，不会再加rowid到索引键上

SQL> create table test_unique (a number);

Table created.

SQL> begin

  2  for i in 1..10000 loop   

  3  insert into test_unique values(i);

  4  end loop;

  5  commit;

  6  end;

7  /

PL/SQL procedure successfully completed.

SQL> create unique index ind_unique on test_unique(a);

Index created.

SQL> select object_id from dba_objects where object_name=upper('ind_unique');

 OBJECT_ID

----------

     32521

SQL> alter session set events 'immediate trace name TREEDUMP level 32521';

Session altered.

*** SESSION ID:(9.208) 2008-05-27 19:28:31.513

----- begin tree dump

branch: 0x100020a 16777738 (0: nrow: 20, level: 1)

   leaf: 0x100020b 16777739 (-1: nrow: 520 rrow: 520)

   leaf: 0x100020c 16777740 (0: nrow: 513 rrow: 513)

   leaf: 0x100020d 16777741 (1: nrow: 513 rrow: 513)

   leaf: 0x100020e 16777742 (2: nrow: 513 rrow: 513)

   leaf: 0x100020f 16777743 (3: nrow: 513 rrow: 513)

   leaf: 0x1000210 16777744 (4: nrow: 513 rrow: 513)

   leaf: 0x1000211 16777745 (5: nrow: 513 rrow: 513)

   leaf: 0x1000212 16777746 (6: nrow: 513 rrow: 513)

   leaf: 0x1000213 16777747 (7: nrow: 513 rrow: 513)

   leaf: 0x1000214 16777748 (8: nrow: 513 rrow: 513)

   leaf: 0x1000215 16777749 (9: nrow: 513 rrow: 513)

   leaf: 0x1000216 16777750 (10: nrow: 513 rrow: 513)

   leaf: 0x1000217 16777751 (11: nrow: 513 rrow: 513)

   leaf: 0x1000218 16777752 (12: nrow: 513 rrow: 513)

   leaf: 0x1000219 16777753 (13: nrow: 513 rrow: 513)

   leaf: 0x100021a 16777754 (14: nrow: 513 rrow: 513)

   leaf: 0x100021b 16777755 (15: nrow: 513 rrow: 513)

   leaf: 0x100021c 16777756 (16: nrow: 513 rrow: 513)

   leaf: 0x100021d 16777757 (17: nrow: 513 rrow: 513)

   leaf: 0x100021e 16777758 (18: nrow: 246 rrow: 246)

----- end tree dump

SQL> select dbms_utility.data_block_address_file(16777738) "file

  2  dbms_utility.data_block_address_block(16777738) "block" from dual;

      file      block

---------- ----------

         4        522

SQL> alter system dump datafile 4 block 522;

System altered.

Branch block dump

=================

header address 183337028=0xaed8044

kdxcolev 1

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 1

kdxcosdc 0

kdxconro 19

kdxcofbo 66=0x42

kdxcofeo 7904=0x1ee0

kdxcoavs 7838

kdxbrlmc 16777739=0x100020b

kdxbrsno 0

kdxbrbksz 8056 

row#0[8048] dba: 16777740=0x100020c

col 0; len 3; (3):  c2 06 16

row#1[8040] dba: 16777741=0x100020d

col 0; len 3; (3):  c2 0b 23

row#2[8032] dba: 16777742=0x100020e

col 0; len 3; (3):  c2 10 30

row#3[8024] dba: 16777743=0x100020f

col 0; len 3; (3):  c2 15 3d

row#4[8016] dba: 16777744=0x1000210

col 0; len 3; (3):  c2 1a 4a

row#5[8008] dba: 16777745=0x1000211

SQL> select dbms_utility.data_block_address_file(16777740) "file

  2  dbms_utility.data_block_address_block(16777740) "block" from dual;

      file      block

---------- ----------

         4        524

SQL> alter system dump datafile 4 block 524;

System altered.

row#0[8048] dba: 16777740=0x100020c

col 0; len 3; (3):  c2 06 16

row#1[8040] dba: 16777741=0x100020d

col 0; len 3; (3):  c2 0b 23

row#2[8032] dba: 16777742=0x100020e

col 0; len 3; (3):  c2 10 30

row#3[8024] dba: 16777743=0x100020f

col 0; len 3; (3):  c2 15 3d

row#4[8016] dba: 16777744=0x1000210

col 0; len 3; (3):  c2 1a 4a

row#5[8008] dba: 16777745=0x1000211

col 0; len 3; (3):  c2 1f 57

row#6[8000] dba: 16777746=0x1000212

col 0; len 3; (3):  c2 24 

row#7[7992] dba: 16777747=0x1000213

col 0; len 3; (3):  c2 2a 0d

row#8[7984] dba: 16777748=0x1000214

col 0; len 3; (3):  c2 2f 1a

row#9[7976] dba: 16777749=0x1000215

col 0; len 3; (3):  c2 34 27

row#10[7968] dba: 16777750=0x1000216

可以看到在唯一索引键中，索引键按升序排序

--EOF--

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/phphot/archive/2008/11/23/3353029.aspx