技术开发 频道

B-Tree 索引中的数据块分裂

  当索引数据块需要分裂时,会从Freelist中找到空闲的数据块满足分配需要,在10224的跟踪文件中,可以看到以下信息记录了新数据块的分配:

splitting leaf,dba 0x03c00419,time 13:58:32.558

  kdisnew_bseg_srch_cbk reject block
-mark full,dba 0x03c00419,time 13:58:32.573

  kdisnew_bseg_srch_cbk rejecting block ,dba
0x03c00419,time 13:58:32.573

  kdisnew_bseg_srch_cbk using block,dba
0x03c0041a,time 13:58:32.573

  如果索引数据块上的数据被全部删除,该数据块就会被放置在freelist的前面,但并不从B树结构上删除:

 HELLODBA.COM> conn demo/demo

  Connected.

  HELLODBA.COM
> truncate table idx_split;

  
Table truncated.

  HELLODBA.COM
> alter session set events '10224 trace name context forever,level 1';

  Session altered.

  HELLODBA.COM
> begin

  
2 for i in 1..64

  
3 loop

  
4 insert into idx_split (a, b, c) values (i*3, lpad('A', 100, 'A'), sysdate);

  
5 end loop;

  
6 end;

  
7 /

  PL
/SQL procedure successfully completed.

  HELLODBA.COM
> commit;

  
Commit complete.

  HELLODBA.COM
> alter session set events 'immediate trace name treedump level 199127';

  Session altered.

  HELLODBA.COM
> delete from idx_split where a between 17*3 and 32*3;

  
16 rows deleted.

  HELLODBA.COM
> commit;

  
Commit complete.

  HELLODBA.COM
> alter session set events 'immediate trace name treedump level 199127';

  Session altered.

  从跟踪文件可以看到:当数据块中的实际记录数(rrow)为0时,被放到了freelist,但是并未从树结构中拿走。

kdimod adding block to free list,dba 0x03c00419,time 14:10:49.785

  
----- begin tree dump

  branch:
0x3c00405 62915589 (0: nrow: 4, level: 1)

  leaf:
0x3c00418 62915608 (-1: nrow: 16 rrow: 16)

  leaf:
0x3c00419 62915609 (0: nrow: 16 rrow: 0)

  leaf:
0x3c0041a 62915610 (1: nrow: 16 rrow: 16)

  leaf:
0x3c0041b 62915611 (2: nrow: 16 rrow: 16)

  
----- end tree dump

  在下一次数据块分裂时,从freelist上获取到该数据块,然后将其从树结构中删除,重新分配到树结构中:

 HELLODBA.COM> insert into idx_split (a, b, c) values (65*3, lpad('A', 100, 'A'), sysdate);

  
1 row created.

  HELLODBA.COM
> commit;

  
Commit complete.

  HELLODBA.COM
> alter session set events 'immediate trace name treedump level 199127';

  Session altered.

  跟踪文件显示了这一数据块被回收利用的过程:

splitting leaf,dba 0x03c0041b,time 14:10:49.831

  kdisprobe
on path succeeded, branch,dba 0x03c00405,time 14:10:49.847

  kdisprobe regot child,dba
0x03c00419,time 14:10:49.847

  kdisdelete probe successful, proceed,dba
0x03c00419,time 14:10:49.863

  
delete leaf,dba 0x03c00419,time 14:10:49.863

  kdisdelbr1 sno
0,dba 0x03c00405,time 14:10:49.863

  kdisnew_bseg_srch_cbk using block,dba
0x03c00419,time 14:10:49.878

  
----- begin tree dump

  branch:
0x3c00405 62915589 (0: nrow: 4, level: 1)

  leaf:
0x3c00418 62915608 (-1: nrow: 16 rrow: 16)

  leaf:
0x3c0041a 62915610 (0: nrow: 16 rrow: 16)

  leaf:
0x3c0041b 62915611 (1: nrow: 16 rrow: 16)

  leaf:
0x3c00419 62915609 (2: nrow: 1 rrow: 1)

  
----- end tree dump

  需要注意的是,数据块被放入freelist的条件是该数据块上的实际记录数(rrow)为0,而不是等待删除这些数据的事务提交:

 HELLODBA.COM> delete from idx_split where a between 17*3 and 32*3;

  
16 rows deleted.

  HELLODBA.COM
> alter session set events 'immediate trace name treedump level 199127';

  Session altered.

  事务未提交,但从跟踪文件可以看到数据块已经被放到freelist上去了:

 kdimod adding block to free list,dba 0x03c0040d,time 14:35:35.582

  
----- begin tree dump

  branch:
0x3c00405 62915589 (0: nrow: 4, level: 1)

  leaf:
0x3c00413 62915603 (-1: nrow: 16 rrow: 16)

  leaf:
0x3c0040d 62915597 (0: nrow: 16 rrow: 0)

  leaf:
0x3c0040e 62915598 (1: nrow: 16 rrow: 16)

  leaf:
0x3c0040f 62915599 (2: nrow: 16 rrow: 16)

  
----- end tree dump

  如果此时发生分裂,因为该数据块在freelist的前列,因此仍然会被获取到,但是,由于其上面的事务并未提交,所以不会被分配: 

splitting leaf,dba 0x03c0040f,time 14:35:35.644

  kdisnew_bseg_srch_cbk rejecting block ,dba
0x03c0040d,time 14:35:35.644

  kdisnew_bseg_srch_cbk reject block
-mark full,dba 0x03c0040f,time 14:35:35.660

  kdisnew_bseg_srch_cbk rejecting block ,dba
0x03c0040f,time 14:35:35.660

  kdisnew_bseg_srch_cbk using block,dba
0x03c00410,time 14:35:35.676

  下一次分裂时,由于其还在freelist,但事务仍未提交,会再次发生这一过程——这就导致了IO的增加: 

splitting leaf,dba 0x03c00410,time 14:35:35.738

  kdisnew_bseg_srch_cbk rejecting block ,dba
0x03c0040d,time 14:35:35.738

  kdisnew_bseg_srch_cbk reject block
-mark full,dba 0x03c00410,time 14:35:35.738

  kdisnew_bseg_srch_cbk rejecting block ,dba
0x03c00410,time 14:35:35.754

  kdisnew_bseg_srch_cbk using block,dba
0x03c00406,time 14:35:35.754

  第二种需要注意的情况是,当删除的空数据块被放置到freelist后(事务也已提交),此时它仍然在树结构中,此时如果有正好属于该数据块在树中位置的数据被插入,数据仍然会被写入该数据块上,但并不从freelist上移走:

 HELLODBA.COM> delete from idx_split where a between 17*3 and 32*3;

  
16 rows deleted.

  HELLODBA.COM
> commit;

  
Commit complete.

  HELLODBA.COM
> insert into idx_split (a, b, c) values (17*3, lpad('A', 100, 'A'), sysdate);

  
1 row created.

  HELLODBA.COM
> alter session set events 'immediate trace name treedump level 199127';

  Session altered.

  HELLODBA.COM
> insert into idx_split (a, b, c) values (65*3, lpad('A', 100, 'A'), sysdate);

  
1 row created.

  HELLODBA.COM
> alter session set events 'immediate trace name treedump level 199127';

  Session altered.

  
--跟踪内容

  kdimod adding block
to free list,dba 0x03c00411,time 14:46:29.181

  
----- begin tree dump

  branch:
0x3c00405 62915589 (0: nrow: 4, level: 1)

  leaf:
0x3c00420 62915616 (-1: nrow: 16 rrow: 16)

  leaf:
0x3c00411 62915601 (0: nrow: 16 rrow: 0)

  leaf:
0x3c00412 62915602 (1: nrow: 16 rrow: 16)

  leaf:
0x3c00413 62915603 (2: nrow: 16 rrow: 16)

  
----- end tree dump

  
*** 2009-10-09 14:46:53.229

  
----- begin tree dump

  branch:
0x3c00405 62915589 (0: nrow: 4, level: 1)

  leaf:
0x3c00420 62915616 (-1: nrow: 16 rrow: 16)

  leaf:
0x3c00411 62915601 (0: nrow: 1 rrow: 1)

  leaf:
0x3c00412 62915602 (1: nrow: 16 rrow: 16)

  leaf:
0x3c00413 62915603 (2: nrow: 16 rrow: 16)

  
----- end tree dump

  此时如果发生分裂,该数据块仍然会被获得,但是分配失败,此时,它才会被从freelist上移走:

splitting leaf,dba 0x03c00413,time 14:47:35.58

  kdisnew_bseg_srch_cbk reject block
-mark full,dba 0x03c00411,time 14:47:35.58

  kdisnew_bseg_srch_cbk rejecting block ,dba
0x03c00411,time 14:47:35.74

  kdisnew_bseg_srch_cbk reject block
-mark full,dba 0x03c00412,time 14:47:35.90

  kdisnew_bseg_srch_cbk rejecting block ,dba
0x03c00412,time 14:47:35.90

  kdisnew_bseg_srch_cbk reject block
-mark full,dba 0x03c00413,time 14:47:35.90

  kdisnew_bseg_srch_cbk rejecting block ,dba
0x03c00413,time 14:47:35.90

  kdisnew_bseg_srch_cbk using block,dba
0x03c00414,time 14:47:35.105
0
相关文章