[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仅仅发生在插入最大的键值，而不是该块中最大的键值(包括块中已删除的索引键值)。