Hello DBA

今天早上有人问我，索引中相同的key是不是按照rowid排序的？我们都知道索引中是按照key的值来进行排序的，但是对于相同的key是如何排序的，我并不是很确定，所以做了一些测试。

构造一张表，每个key都在不同的block上，方便测试。

create table test (id number,name varchar2(10));
create index test_id_ind on test(id);
insert into test values(1,’block1′);
insert into test values(2,’block1′);
insert into test values(3,’block1′);

alter table test minimize records_per_block;

insert into test values(1,’block2′);
insert into test values(2,’block2′);
insert into test values(3,’block2′);
insert into test values(1,’block3′);
insert into test values(2,’block3′);
insert into test values(3,’block3′);

select DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) block,id from test;

BLOCK         ID
———- ———-
80698          1
80698          2
80698          3
80699          1
80699          1
80699          3
80700          1
80700          2
80700          3

如何找到leaf block对应的file#和block#，通过object_id dump出索引的结构信息

ALTER SESSION SET EVENTS  ‘immediate trace name TREEDUMP level object_id’;

—– begin tree dump
leaf: 0×4013cfa 67190010 (0: nrow: 9 rrow: 9)
—– end tree dump

因为索引很小，只有一个block即leaf block

select dbms_utility.data_block_address_file(67190010) file#,
dbms_utility.data_block_address_block(67190010) block# from dual

FILE#     BLOCK#
———- ———-
16      81146

alter system dump datafile 16 block 81146;

row#0[8020] flag: —–, lock: 2
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  04 01 3b 3a 00 00
row#1[7984] flag: —–, lock: 2
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  04 01 3b 3b 00 00
row#2[7948] flag: —–, lock: 2
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  04 01 3b 3c 00 00
row#3[8008] flag: —–, lock: 2
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  04 01 3b 3a 00 01
row#4[7972] flag: —–, lock: 2
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  04 01 3b 3b 00 01
row#5[7936] flag: —–, lock: 2
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  04 01 3b 3c 00 01
row#6[7996] flag: —–, lock: 2
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  04 01 3b 3a 00 02
row#7[7960] flag: —–, lock: 2
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  04 01 3b 3b 00 02
row#8[7924] flag: —–, lock: 2
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  04 01 3b 3c 00 02

我们可以看到是按照key和rowid排序的，我们再做一个update操作，看看效果如何。

update test set id=1 where id=2 and name=’block2′;

row#0[8020] flag: —–, lock: 0
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  04 01 3b 3a 00 00
row#1[7984] flag: —–, lock: 0
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  04 01 3b 3b 00 00
row#2[7912] flag: —–, lock: 2
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  04 01 3b 3b 00 01
row#3[7948] flag: —–, lock: 0
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  04 01 3b 3c 00 00
row#4[8008] flag: —–, lock: 0
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  04 01 3b 3a 00 01
row#5[7972] flag: —D-, lock: 2
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  04 01 3b 3b 00 01
row#6[7936] flag: —–, lock: 0
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  04 01 3b 3c 00 01
row#7[7996] flag: —–, lock: 0
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  04 01 3b 3a 00 02
row#8[7960] flag: —–, lock: 0
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  04 01 3b 3b 00 02
row#9[7924] flag: —–, lock: 0
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  04 01 3b 3c 00 02

可以看到原来的一行被删除，并在适当的位置新插入了一条（注意红色部分），这样就证明索引中相同的key是按照rowid排序的，后来在文档中也找到了这个问题的证明：Multiple rows with identical values are sorted in ascending order by rowid.

Oracle这样做会带来什么好处？我们理所当然的会想到，对于相同的key又在同一个block中的column，那么只需要访问这个block一次就可以了。而不需要每行访问block一次，这样就可以在某些情况下降低逻辑读，打开autotrace很容易得到证明。

后来在网上搜了一下，才发现七公早就对这个问题做过测试了，非常详细。有兴趣的可以去看：non-unique index branch and leaf block structure

–EOF–