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

记得在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 16777642 (0: nrow: 29, level: 1)
leaf: 0x10001ab 16777643 (-1: nrow: 485 rrow: 485)
leaf: 0x10001ac 16777644 (0: nrow: 479 rrow: 479)
leaf: 0x10001ad 16777645 (1: nrow: 510 rrow: 510)
leaf: 0x10001ae 16777646 (2: nrow: 512 rrow: 512)
leaf: 0x10001af 16777647 (3: nrow: 512 rrow: 512)
leaf: 0x10001b0 16777648 (4: nrow: 512 rrow: 512)
leaf: 0x10001b1 16777649 (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 16777664 (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(16777642) “file”,
2 dbms_utility.data_block_address_block(16777642) “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 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 28
kdxcofbo 84=0×54
kdxcofeo 7769=0x1e59
kdxcoavs 7685
kdxbrlmc 16777643=0x10001ab 第一个leaf block,下面共列28个叶子块(row#0—-row#28)
kdxbrsno 0
kdxbrbksz 8056
row#0[8047] dba: 16777644=0x10001ac
col 0; len 3; (3): c2 05 57
col 1; TERM
row#1[8038] dba: 16777645=0x10001ad
col 0; len 3; (3): c2 0a 42
col 1; TERM
row#2[8024] dba: 16777646=0x10001ae
col 0; len 2; (2): c2 0b
col 1; len 6; (6): 01 00 01 a1 02 3e
row#3[8010] dba: 16777647=0x10001af
col 0; len 2; (2): c2 0b
col 1; len 6; (6): 01 00 01 a2 01 aa
row#4[7996] dba: 16777648=0x10001b0
col 0; len 2; (2): c2 0b
col 1; len 6; (6): 01 00 01 a3 01 16
row#5[7982] dba: 16777649=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[7895] 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[2644] 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 89
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: 0×1000210 16777744 (4: nrow: 513 rrow: 513)
leaf: 0×1000211 16777745 (5: nrow: 513 rrow: 513)
leaf: 0×1000212 16777746 (6: nrow: 513 rrow: 513)
leaf: 0×1000213 16777747 (7: nrow: 513 rrow: 513)
leaf: 0×1000214 16777748 (8: nrow: 513 rrow: 513)
leaf: 0×1000215 16777749 (9: nrow: 513 rrow: 513)
leaf: 0×1000216 16777750 (10: nrow: 513 rrow: 513)
leaf: 0×1000217 16777751 (11: nrow: 513 rrow: 513)
leaf: 0×1000218 16777752 (12: nrow: 513 rrow: 513)
leaf: 0×1000219 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 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 19
kdxcofbo 66=0×42
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=0×1000210
col 0; len 3; (3): c2 1a 4a
row#5[8008] dba: 16777745=0×1000211

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=0×1000210
col 0; len 3; (3): c2 1a 4a
row#5[8008] dba: 16777745=0×1000211
col 0; len 3; (3): c2 1f 57
row#6[8000] dba: 16777746=0×1000212
col 0; len 3; (3): c2 24 64
row#7[7992] dba: 16777747=0×1000213
col 0; len 3; (3): c2 2a 0d
row#8[7984] dba: 16777748=0×1000214
col 0; len 3; (3): c2 2f 1a
row#9[7976] dba: 16777749=0×1000215
col 0; len 3; (3): c2 34 27
row#10[7968] dba: 16777750=0×1000216

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