深入了解B*树索引50-50坼分和90-10坼分

 

Oracle的B*树索引在Dml操作时，会对索引的平衡树进行坼分或修改动作。典型的索引坼分有50-50坼分和90-10坼分，90-10坼分亦可称为99-01坼分。

90-10坼分：指的是新插入的键值是最大值，或者修改某键值为最大值时，当当前最大的索引块不存在剩余空间时，Oracle会新增一个索引块，将新的最大值插入到新的索引块中，但不会存在50-50坼分的数据迁移过程。以下是90-10坼分的定义及图形演示：

 

50-50坼分：指的是当新插入的或修改的键值不是索引键值的最大值，同时当要插入的键值的索引块没有剩余空间的时候，Oracle会新增一个索引块，然后将前一个索引块的一半内容迁移到新增的一个索引块。

以下是50-50坼分的定义及图形演示：

 

二、实验

基础环境

SQL> set timing on;
SQL> set serveroutput on;
SQL> create table ou_block as select rownum rn , object_id from Dba_Objects;
 
Table created
 
Executed in 0.328 seconds
 
SQL> create index idx_ou_block on ou_block (rn);
 
Index created
 
Executed in 0.718 seconds
 
SQL> select object_id from Dba_Objects where object_name = 'IDX_OU_BLOCK';
 
 OBJECT_ID
----------
     56229
 
Executed in 0.047 seconds

SQL> alter session set events 'immediate trace name treedump level 56229';
 
Session altered
 
Executed in 0.156 seconds
 
SQL> select get_trace from dual;
 
GET_TRACE
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\UDUMP/orcl_ora_6468.trc
 
Executed in 0.405 seconds

此时查找orcl_ora_6488.trc日志文件的内容，注意的是我们打开的是索引的信息，而非表的信息。部分内容如下：

dump开头

----- begin tree dump
branch: 0x1007d5c 16809308 (0: nrow: 120, level: 1)
   leaf: 0x1007d5d 16809309 (-1: nrow: 485 rrow: 485)
   leaf: 0x1007d5e 16809310 (0: nrow: 479 rrow: 479)

dump结尾
   leaf: 0x1007b50 16808784 (115: nrow: 449 rrow: 449)
   leaf: 0x1007c0b 16808971 (116: nrow: 449 rrow: 449)
   leaf: 0x1007c0c 16808972 (117: nrow: 449 rrow: 449)
   leaf: 0x1007c0d 16808973 (118: nrow: 72 rrow: 72)
----- end tree dump
branch ：分支

leaf：代表叶节点

0x1007d5c：分支块号

nrow ：总行数

rrow：当前行数

现根据叶子节点的块号查找其信息0x1007d5d

SQL> select get_fb('0x1007d5d') from dual;
 
GET_FB('0X1007D5D')
--------------------------------------------------------------------------------
datafile# is:4
datablock is:32093
dump command:alter system dump datafile 4 block 32093;
 
 
Executed in 0.14 seconds
 
SQL> alter system dump datafile 4 block 32093;
 
System altered
 
Executed in 0.25 seconds

此时trc文件相关叶子节点内容如下：

Leaf block dump
===============
header address 82674788=0x4ed8464
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 485
kdxcofbo 1006=0x3ee
kdxcofeo 1834=0x72a
kdxcoavs 828
kdxlespl 0
kdxlende 0
kdxlenxt 16809310=0x1007d5e
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  01 00 7c e4 00 00
row#1[8012] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 00 7c e4 00 01

 

如上信息：row#0表示当前块叶子节点第一条记录，它存放信息有2条，

1：col 0; len 2; (2):  c1 02   col0存放的是键值，c1 02表示键值内容

2：col 1; len 6; (6):  01 00 7c e4 00 00 col1存放的是rowid值

 

SQL> select dump(1,16) from dual;
 
DUMP(1,16)
-----------------
Typ=2 Len=2: c1,2
 
Executed in 0.016 seconds
 

通过dump函数就可知道1=c1 02，16代表16进制，即当前叶子节点的键值rn=1

亦可通过一下pl/sql块反推值：

SQL> declare n number;
  2        begin
  3          dbms_stats.convert_raw_value('c102',n);
  4          dbms_output.put_line(n);
  5         end;
  6  /
 
1
 
PL/SQL procedure successfully completed
 
Executed in 0 seconds

 

那如和查找rowid号呢？如下：

SQL> select idx_rowid(' 01 00 7c e4 00 00') from dual;
 
IDX_ROWID('01007CE40000')
--------------------------------------------------------------------------------
File# = 4, Block# = 31972, Row# = 0
 
Executed in 0.265 seconds
 
SQL>
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_row_number(rowid) from
  2  ou_block where rn =1;
 
DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER( DBMS_ROWID.ROWID_ROW_NUMBER(RO
------------------------------ ------------------------------ ------------------------------
                             4                          31972                              0
 
Executed in 0.218 seconds

 

下面开始做实验

实验1：插入最小值，看索引块是90-10坼分还是50-50坼分

通常情况下一个索引块会保留意见10%的剩余空间，那插入多少值为好，上面第一个叶子节点如下：

leaf: 0x1007d5d 16809309 (-1: nrow: 485 rrow: 485)

通过计算得知如果插入60条记录Oracle就会进行坼分

 

  SQL> declare
  2  begin
  3    for i in 1..60 loop
  4      insert into ou_block values (-i,1000);
  5    end loop;
  6    commit;
  7  end;
  8  /
 
PL/SQL procedure successfully completed
 
Executed in 0.016 seconds
 
SQL> select object_id from Dba_Objects where object_name = 'IDX_OU_BLOCK';
 
 OBJECT_ID
----------
     56229
 
Executed in 0.047 seconds
 
 
SQL> alter session set events 'immediate trace name treedump level 56229';
 
Session altered
 
Executed in 0.156 seconds

此时发现trc文件内容如下：

*** 2013-04-20 10:03:30.958
----- begin tree dump
branch: 0x1007d5c 16809308 (0: nrow: 121, level: 1)
   leaf: 0x1007d5d 16809309 (-1: nrow: 283 rrow: 283)
   leaf: 0x1007c1d 16808989 (0: nrow: 262 rrow: 262)
   leaf: 0x1007d5e 16809310 (1: nrow: 479 rrow: 479)

由此可证明其是50-50坼分。

 

 

实验2：插入最大值，验证其是90-10坼分

当前叶子节点最大值存储如下：

   leaf: 0x1007c0b 16808971 (117: nrow: 449 rrow: 449)
   leaf: 0x1007c0c 16808972 (118: nrow: 449 rrow: 449)
   leaf: 0x1007c0d 16808973 (119: nrow: 72 rrow: 72)
----- end tree dump

假如插入最大值400条记录会不会发生坼分呢？

 

SQL> select max(rn) from ou_block;
 
   MAX(RN)
----------
     54135
 
Executed in 0.032 seconds
 
SQL> declare
  2    begin
  3     for i in 54136..54536 loop
  4       insert into ou_block values (i,1000);
  5     end loop;
  6   commit;
  7  end;
  8  /
 
PL/SQL procedure successfully completed
 
Executed in 0.016 seconds

SQL> alter session set events 'immediate trace name treedump level 56229';
 
Session altered
 
Executed in 0.124 seconds

 

此时查看trc如下：

   leaf: 0x1007b50 16808784 (116: nrow: 449 rrow: 449)
   leaf: 0x1007c0b 16808971 (117: nrow: 449 rrow: 449)
   leaf: 0x1007c0c 16808972 (118: nrow: 449 rrow: 449)
   leaf: 0x1007c0d 16808973 (119: nrow: 473 rrow: 473)
----- end tree dump

发现并没有新增一个块，那是为什么呢，449+449/1是不是大于>473，表示插入400条记录后，叶子节点还未满块，所以未进行坼分，那继续插入数据如下： 
SQL> select max(rn) from ou_block;
 
   MAX(RN)
----------
     54536
 
Executed in 0.047 seconds
 
SQL> declare
  2    begin
  3      for i in 54537..54737 loop
  4        insert into ou_block values (i,1000);
  5     end loop;
  6  commit;
  7  end;
  8  /
 
PL/SQL procedure successfully completed
 
Executed in 0 seconds
 
SQL> alter session set events 'immediate trace name treedump level 56229';
 
Session altered
 
Executed in 0.125 seconds

此时查看trc如下：

   leaf: 0x1007c0c 16808972 (118: nrow: 449 rrow: 449)
   leaf: 0x1007c0d 16808973 (119: nrow: 500 rrow: 500)
   leaf: 0x1007c21 16808993 (120: nrow: 174 rrow: 174)
----- end tree dump

发现新增一个块，走的是90-10坼分。

 

实验3：验证dml操作，索引叶子节点的变化情况

当delete后，索引的叶子节点行数会不会删除呢，实验步骤如下：

SQL> delete from ou_block where mod(rn, 3) = 1;
 
18246 rows deleted
 
Executed in 0.624 seconds
 
SQL> commit;
 
Commit complete
 
Executed in 0 seconds
 
SQL> alter session set events 'immediate trace name treedump level 56229';
 
Session altered
 
Executed in 0.14 seconds
 查看trc内容如下：

 

*** 2013-04-20 10:35:23.702
----- begin tree dump
branch: 0x1007d5c 16809308 (0: nrow: 122, level: 1)
   leaf: 0x1007d5d 16809309 (-1: nrow: 283 rrow: 208)
   leaf: 0x1007c1d 16808989 (0: nrow: 262 rrow: 175)
   leaf: 0x1007d5e 16809310 (1: nrow: 479 rrow: 319)

发现记录数变少了，那是因为delete删除了一部分数据，那我们现在查看叶子节点块在内容呢，是否还存在相应的资源呢？如下：

SQL> select get_fb('0x1007d5d') from dual;
 
GET_FB('0X1007D5D')
--------------------------------------------------------------------------------
datafile# is:4
datablock is:32093
dump command:alter system dump datafile 4 block 32093;
 
 
Executed in 0.062 seconds
 
SQL> alter system dump datafile 4 block 32093;
 
System altered
 
Executed in 0.031 seconds
 
此时查看trc内容如下：

row#279[7984] flag: ---D--, lock: 2, len=13
col 0; len 3; (3):  c2 03 15
col 1; len 6; (6):  01 00 7c e4 00 db
row#280[7997] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 03 16
col 1; len 6; (6):  01 00 7c e4 00 dc
row#281[8010] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 03 17
col 1; len 6; (6):  01 00 7c e4 00 dd
row#282[8023] flag: ---D--, lock: 2, len=13
col 0; len 3; (3):  c2 03 18
col 1; len 6; (6):  01 00 7c e4 00 de
----- end of leaf block dump -----

发现多了一个D的字符，且行数没有减少，由此可见，delete操作并不会对索引块的叶子节点进行删除，它只做了一个标记，代表已删除的数据。

又一个问题，update索引键值，叶子节点会怎么变？供大家思考。