键值重复率高的B树索引构建过程和查询操作分析

 

                                

                                  liuyu（2014-09-16）

 

 

创建测试表：

SQL> create table t_liu(id number,namevarchar2(100));

Table created.

 

SQL> createindex i_liu on t_liu(id);

 

 

SQL> selectobject_id from dba_objects where object_name='I_LIU';

 

 OBJECT_ID

----------

   150956

Index created.

---插入10条数据----

begin

for i in 1 .. 10 loop

insert into t_liu values(i,'xxx');

commit;

end loop;

end;

/

SQL> altersession set events 'immediate trace name treedump level 150956';

 

使用下面的脚本查看trc文件的位置：

 SELECT    d.VALUE

  || '/'

  || LOWER (RTRIM (i.INSTANCE, CHR (0)))

  || '_ora_'

 || p.spid

 || '.trc' trace_file_name

  FROM (SELECT p.spid

  FROM SYS.v_$mystat m,SYS.v_$session s, SYS.v_$process p

  WHERE m.statistic# =1 AND s.SID = m.SID AND p.addr = s.paddr) p,

  (SELECT t.INSTANCE

  FROM SYS.v_$thread t,SYS.v_$parameter v

  WHERE v.NAME ='thread'

  AND (v.VALUE = 0 ORt.thread# = TO_NUMBER (v.VALUE))) i,

  (SELECT VALUE

  FROM SYS.v_$parameter

  WHERE NAME = 'user_dump_dest')d ;

 

/u01/oracle/diag/rdbms/test/test/trace/test_ora_32698.trc

 

----- begin tree dump

leaf: 0x1077233 17265203 (0: nrow: 10 rrow:10)

----- end tree dump

 

    这里发现只有一个叶子节点，因为数据只有10条，root，branch，和leaf，只存在一个leaf块内。

 

begin

for i in 1 .. 1000 loop

insert into t_liu values(i,'xxx');

commit;

end loop;

end;

/

此时的索引已经分裂：

 

*** 2014-09-16 10:02:38.266

branch: 0x1077233 17265203 (0: nrow: 2,level: 1)

  leaf: 0x1077236 17265206 (-1: nrow: 540 rrow: 540)

  leaf: 0x1077237 17265207 (0: nrow: 470 rrow: 470)

----- end tree dump

 

 

这里测试，往里面插入最大值1000

begin

for i in 1 .. 10000 loop

insert into t_liu values(1000,'xxx');

commit;

end loop;

end;

/

 

----- begin tree dump

branch: 0x1077233 17265203 (0: nrow: 29,level: 1)

  leaf: 0x1077236 17265206 (-1: nrow: 540 rrow: 540)

  leaf: 0x1077237 17265207 (0: nrow: 269 rrow: 269)

  leaf: 0x1077256 17265238 (1: nrow: 277 rrow: 277)

  leaf: 0x1077257 17265239 (2: nrow: 291 rrow: 291)

  leaf: 0x1077250 17265232 (3: nrow: 291 rrow: 291)

  leaf: 0x1077251 17265233 (4: nrow: 291 rrow: 291)

  leaf: 0x1077252 17265234 (5: nrow: 328 rrow: 328)

  leaf: 0x1077234 17265204 (6: nrow: 571 rrow: 571)

  leaf: 0x1077235 17265205 (7: nrow: 291 rrow: 291)

  leaf: 0x1077262 17265250 (8: nrow: 291 rrow: 291)

  leaf: 0x1077263 17265251 (9: nrow: 291 rrow: 291)

  leaf: 0x1077269 17265257 (10: nrow: 291 rrow: 291)

  leaf: 0x107726a 17265258 (11: nrow: 314 rrow: 314)

  leaf: 0x1077264 17265252 (12: nrow: 291 rrow: 291)

  leaf: 0x1077265 17265253 (13: nrow: 291 rrow: 291)

  leaf: 0x1077266 17265254 (14: nrow: 291 rrow: 291)

  leaf: 0x1077267 17265255 (15: nrow: 291 rrow: 291)

  leaf: 0x1077261 17265249 (16: nrow: 291 rrow: 291)

  leaf: 0x107726e 17265262 (17: nrow: 291 rrow: 291)

  leaf: 0x107726f 17265263 (18: nrow: 291 rrow: 291)

  leaf: 0x1077268 17265256 (19: nrow: 405 rrow: 405)

  leaf: 0x1077255 17265237 (20: nrow: 281 rrow: 281)

  leaf: 0x1077253 17265235 (21: nrow: 571 rrow: 571)

  leaf: 0x1077254 17265236 (22: nrow: 571 rrow: 571)

  leaf: 0x107726b 17265259 (23: nrow: 571 rrow: 571)

  leaf: 0x107726c 17265260 (24: nrow: 571 rrow: 571)

  leaf: 0x107726d 17265261 (25: nrow: 571 rrow: 571)

  leaf: 0x107727a 17265274 (26: nrow: 571 rrow: 571)

  leaf: 0x107727b 17265275 (27: nrow: 525 rrow: 525)

----- end tree dump

 

解释trc文件

               每一行第一列表示：节点类型，branch是分支节点（包括了根节点），而leaf则是叶子节点

               第二列表示：节点地址，16进制

               第三列表示：节点地址，10进制

               第四列表示：相对于前一个节点的位置：根节点从0算起，其他分支节点和叶子节点从1开始算

               第五列表示：（nrow）当前节点所含索引条目的数量（包括delete的条目）

               第六列表示：（level）分支节点的层级，在oracle的索引中，层级号是倒过来的，也就是说假设某个索引有N层，则根节点的层级号为N，而根节点下一层的分支节点的层级号为N-1