non-unique index branch and leaf block structure

http://www.orawh.com/134.html

Index Range Scans

An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns.
Multiple rows with identical values are sorted in ascending order by rowid。

我以前也看过这段话，但是也就简单的过去了，没有细想过，所以当小霸王问为什么相同值会按照rowid排序的时候我也没给出特别明确的答复。做了以下实验，应该可以给小霸王
一个满意的答复了。

在考虑这个问题的时候我们首先要回顾的知识点是unique index和non-unique index它的构造是不一样
的。对于unique index，它的branch block里面只保存key value和leaf block的address，因为根据这
2个值就可以定位当新值插入时会选择哪个leaf block进行插入，leaf block里面的值也没必要按照rowid的顺序排列了，只需要按照key value排序就行了。但是如果是non-unique index,branch block
里面必须保存key value,leaf block的address,和rowid。在leaf block里面如果key value相同的话
要按照rowid做升序排列，我个人觉得这样做会有2个好处，一个是可以提高相同一个leaf block内相同key value能尽量关联到相同的data block。第2个好处是当插入新的相同key value时能很容易定位插入到哪个block。为了能做到这些，non-unique index必须在branch block里面放入rowid。下面来看一下实验。
首先我们需要创建一张测试表

SQL> create table test(a number);

Table created.

插入2000条数据，值为1到2000

SQL> begin                    
  2  for i in 1..2000 loop   
  3  insert into test values(i);
  4  end loop;                
  5  commit;                  
  6  end;                     
  7  /                        

PL/SQL procedure successfully completed.
再插入500条值为500的数据，这样的话这个表存在501条值为500的记录

SQL> begin                    
  2  for i in 1..500 loop   
  3  insert into test values(500);
  4  end loop;                
  5  commit;                  
  6  end;                     
  7  /                        

PL/SQL procedure successfully completed.                       
SQL>

创建一个non-unique index

SQL> create index ind_test on test(a);

Index created.

查看non-unique index的object_id

SQL> select object_id from dba_objects where object_name=’IND_TEST’;

OBJECT_ID
———-
     54032

dump non-unique index的层级结构

SQL>  ALTER SESSION SET EVENTS ‘immediate trace name TREEDUMP level 54032′;

Session altered.

—– begin tree dump
branch: 0×1000044 16777284 (0: nrow: 6, level: 1)
   leaf: 0×1000045 16777285 (-1: nrow: 279 rrow: 279)
   leaf: 0×100004d 16777293 (0: nrow: 275 rrow: 275)
   leaf: 0×100004e 16777294 (1: nrow: 486 rrow: 486)
   leaf: 0×1000046 16777286 (2: nrow: 533 rrow: 533)
   leaf: 0×1000048 16777288 (3: nrow: 533 rrow: 533)
   leaf: 0×1000047 16777287 (4: nrow: 394 rrow: 394)
—– end tree dump

看一下branch block的所在文件和块号，准备dump

SQL> select dbms_utility.data_block_address_file(16777284) “file”,
  2  dbms_utility.data_block_address_block(16777284) “block” from dual;

      file      block
———- ———-
         4         68

dump branch block的实际内容

SQL> alter system dump datafile 4 block 68;

System altered.
分析一下branch block，可以看到leaf block是从kdxbrlmc 16777285这个地址开始
dba: 16777294这个块与其他块有点不同，它的col1保存的就是rowid 01 00 00 3e 00 4a
，这里的col0 c2 06转换成10进制的值就是500，为什么其他leaf block的col1都被置为
TERM省略掉了呢，因为只有16777293，16777294这两个块保存了500这个重复值，所以当继续
插入500时，oracle可以根据这边的col1来定位是插入到16777293还是插入到16777294。插入
其他值并不受这个影响，所以oracle对这个地方做了优化，并不是所有non-unique branch block
都是需要记录rowid的。