oracle index split

昨日跟原来一同事聊到index split这个话题，过去一直没关注过，今天借空闲机会学习了下，下面记录下实验的过程。
50-50 split
Root block, branch block are always in 50-50 split when the block is full.
If the new value of index is not maximum value of the index, the leaf block is in 50-50 split when the block is full


90-10 split
If the new value of index is maximum value of the index, the leaf block is in 90-10 split when the block is full


按照index split时数据块上分布的数据比例，分为5-5分裂和9-1分裂，下面将人为制造一下这两种方式：

DB：

select * from v$version;  
  
BANNER  
----------------------------------------------------------------  
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production  
PL/SQL Release 9.2.0.8.0 - Production  
CORE    9.2.0.8.0       Production  
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production  
NLSRTL Version 9.2.0.8.0 - Production  

1、9-1分裂：

set lines 200  
select a.*,b.name from v$mystat a , v$statname b   
where a.statistic#=b.statistic#   
and b.name like '%splits%';  
  
       SID STATISTIC#      VALUE NAME  
---------- ---------- ---------- ----------------------------------------------------------------  
       118        201          0 leaf node splits  
       118        202          0 leaf node 90-10 splits  
       118        203          0 branch node splits  
  
  
declare  
  i number;  
begin  
  for i in 1..50000  
  loop  
    insert into perfstat.hcn_t1 values('XX',i);  
  end loop;  
end;  
/    
  
  
PL/SQL procedure successfully completed.  

set lines 200  
select a.*,b.name from v$mystat a , v$statname b   
where a.statistic#=b.statistic#   
and b.name like '%splits%';  
  
  
       SID STATISTIC#      VALUE NAME  
---------- ---------- ---------- ----------------------------------------------------------------  
       118        201         98 leaf node splits  
       118        202         98 leaf node 90-10 splits  
       118        203          0 branch node splits  

一共发生了98次leaf node splits，都是leaf node 90-10 splits

2、5-5分裂
可以在插入时采用完全降序的方式  

create table perfstat.t2 (name varchar2(10),id number) pctfree 0;

Table created.

create index perfstat.i2_hcn_t2 on perfstat.t2(id);

Index created.

set lines 200
select a.*,b.name from v$mystat a , v$statname b 
where a.statistic#=b.statistic# 
and b.name like '%splits%';

       SID STATISTIC#      VALUE NAME
---------- ---------- ---------- ----------------------------------------------------------------
       118        201         98 leaf node splits
       118        202         98 leaf node 90-10 splits
       118        203          0 branch node splits


analyze index perfstat.i2_hcn_t2 validate structure;

Index analyzed.

select blocks, lf_blks, pct_used,height  from index_stats;

    BLOCKS    LF_BLKS   PCT_USED     HEIGHT
---------- ---------- ---------- ----------
       128          1                     1


declare
  i number;      
begin
  for i in reverse 1..50000
  loop
    insert into perfstat.t2 values('XX',i);
  end loop;
end;
/   

PL/SQL procedure successfully completed.

analyze index perfstat.i2_hcn_t2 validate structure;

Index analyzed.

select blocks, lf_blks, pct_used,height  from index_stats;

    BLOCKS    LF_BLKS   PCT_USED     HEIGHT
---------- ---------- ---------- ----------
       256        200         50          2

select a.*,b.name from v$mystat a , v$statname b 
where a.statistic#=b.statistic# 
and b.name like '%splits%';

       SID STATISTIC#      VALUE NAME
---------- ---------- ---------- ----------------------------------------------------------------
       118        201        297 leaf node splits
       118        202         98 leaf node 90-10 splits
       118        203          0 branch node splits

可以看出一共执行了297-98=199次leaf node splits，都不属于leaf node 90-10 splits。
这次实验只能通过在v$mystat中的leaf node splits和leaf node 90-10 splits来说明，实验还不够完善，
准备通过treedump的方式来证明一下，准备在下一篇中说明，听说9i中index splits的算法存在缺陷，
据说在10g中会有所不同,可以再测试一下也能借此看看，O(∩_∩)O哈！