[20131015]关于索引块分裂.txt

前几天看了链接：http://www.askmaclean.com/archives/index-split.html

提到：
oracle中的索引块分裂主要分成 以下几种：

    leaf node 90-10 splits
    leaf node 50-50 splits
    branch node splits
    root node splits


按照 leaf Block Split 分裂时的行为 又可以分为：

leaf node 90-10 splits    插入到索引leaf block叶子块中的索引键是该块中最大的键值(包括块中已删除的索引键值)。 在此种情况
下实施 90-10 split( 实际是 99-1 )，原叶子块仍保持99%的full， 而到另一个空的叶子块中插入该条新的最大键值记录。

--我一直以为仅仅插入键值最大，并且该索引块已经满的情况下出现90-10 splits。而不是该块中最大的键值。
--自己做一个例子测试看看。

SCOTT@test> @ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t as select rownum id,'testtest' name from dual connect by level

create index i_t_all pctfree 0 on t(id,name) pctfree 0;

SCOTT@test> select * from dba_objects where wner=user and object_name='I_T_ALL';

OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME

------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------

SCOTT  I_T_ALL                             274898         274898 INDEX               2013-10-15 15:51:30 2013-10-15 15:51:30 2013-10-15:15:51:30 VALID   N N N          4

SCOTT@test> alter session set events 'immediate trace name treedump level 274898';

Session altered.

--查看跟踪文件内容如下：
----- begin tree dump
branch: 0x1000213 16777747 (0: nrow: 3, level: 1)
   leaf: 0x1000214 16777748 (-1: nrow: 337 rrow: 337)
   leaf: 0x1000215 16777749 (0: nrow: 333 rrow: 333)
   leaf: 0x1000216 16777750 (1: nrow: 330 rrow: 330)
----- end tree dump

第1个节点满放337条键值。

SCOTT@test> @dfb 1000214
old   2: dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx')) rfile#,
new   2: dbms_utility.data_block_address_file(to_number('1000214','xxxxxxxxxxxxxxxx')) rfile#,
old   3: dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) block#
new   3: dbms_utility.data_block_address_block(to_number('1000214','xxxxxxxxxxxxxxxx')) block#
    RFILE#     BLOCK#
---------- ----------
         4        532

old   1: select 'alter system dump datafile '||dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx'))||' block '||
new   1: select 'alter system dump datafile '||dbms_utility.data_block_address_file(to_number('1000214','xxxxxxxxxxxxxxxx'))||' block '||
old   2: dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) ||' ;' text
new   2: dbms_utility.data_block_address_block(to_number('1000214','xxxxxxxxxxxxxxxx')) ||' ;' text
TEXT
--------------------------------------------------------------------------------------------------------------------
alter system dump datafile 4 block 532 ;


SCOTT@test> alter system dump datafile 4 block 532 ;
System altered.

--查看跟踪文件内容如下：
row#0[8011] flag: ------, lock: 0, len=21
col 0; len 2; (2):  c1 02
col 1; len 8; (8):  74 65 73 74 74 65 73 74
col 2; len 6; (6):  01 00 02 0b 00 00
...
row#336[720] flag: ------, lock: 0, len=22
col 0; len 3; (3):  c2 04 26
col 1; len 8; (8):  74 65 73 74 74 65 73 74
col 2; len 6; (6):  01 00 02 0b 01 50
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 532 maxblk 532

--转储情况也是一致的。

2.这样新建立表T1：
create table t1 as select * from t  where id338 order by id;
create index i_t1_all on t1(id,name) pctfree 0 ;

SCOTT@test> select * from dba_objects where wner=user and object_name='I_T1_ALL';

OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME

------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------

SCOTT  I_T1_ALL                            274905         274905 INDEX               2013-10-15 16:21:10 2013-10-15 16:21:10 2013-10-15:16:21:10 VALID   N N N          4

SCOTT@test> alter session set events 'immediate trace name treedump level 274905';
Session altered.

--查看跟踪文件内容如下：
----- begin tree dump
branch: 0x1000223 16777763 (0: nrow: 3, level: 1)
   leaf: 0x1000224 16777764 (-1: nrow: 337 rrow: 337)
   leaf: 0x1000225 16777765 (0: nrow: 333 rrow: 333)
   leaf: 0x1000226 16777766 (1: nrow: 329 rrow: 329)
----- end tree dump

--这样如果插入：insert into t1 values (337,'testtest');看看索引的分裂是什么形式？
--退出再进入：

SCOTT@test> column name format a30

SCOTT@test> select a.*,b.name from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like '%leaf%';

       SID STATISTIC#      VALUE NAME

---------- ---------- ---------- ------------------------------

       192        412          0 leaf node splits

       192        413          0 leaf node 90-10 splits

SCOTT@test> insert into t1 values (338,'testtest');
1 row created.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> select a.*,b.name from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like '%leaf%';

       SID STATISTIC#      VALUE NAME

---------- ---------- ---------- ------------------------------

       192        412          1 leaf node splits

       192        413          0 leaf node 90-10 splits

--发生了分裂。但是并不是90-10 splits。而是50-50分裂！

----- begin tree dump
branch: 0x1000223 16777763 (0: nrow: 4, level: 1)
   leaf: 0x1000224 16777764 (-1: nrow: 176 rrow: 176)
   leaf: 0x1000227 16777767 (0: nrow: 162 rrow: 162)
   leaf: 0x1000225 16777765 (1: nrow: 333 rrow: 333)
   leaf: 0x1000226 16777766 (2: nrow: 329 rrow: 329)
----- end tree dump

-- 176 + 162  = 338 结果也正确。
--测试说明leaf node 90-10 splits仅仅发生在插入最大的键值，而不是该块中最大的键值(包括块中已删除的索引键值)。