深入了解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坼分的定义及图形演示:
50-50def
 
二、实验
基础环境
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索引键值,叶子节点会怎么变?供大家思考。
 
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值