[201112114]index leaf node 50-50 split

index leaf node  50-50 split

[201112114]index leaf node  50-50 split.txt

當索引leaf滿分裂時,存在兩種情況：
1.如果插入的鍵值是最大值,分裂按照90-10 split.
2.如果不是,按照50-50分裂。

如何大量出現50－50的分裂呢？實際上的測試的例子很簡單,如果先插入一個異常大的鍵值,插入鍵值是線性增加的,就可以模擬大量50－50的分裂然後如下：

1.建立測試表:

select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

create table t1(a varchar2(5),b varchar2(10));
create unique index i_t1_a on t1(a);

2.插入一個異常數值:

insert into t1 values ('99999','test');
commit;
--插入一個異常大的數值。

3.繼續新增資料:

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
---------- ---------- ---------- ----------------------------------------------------------------
        11        382          0 leaf node splits
        11        383          0 leaf node 90-10 splits

begin
        for i in 1..10000   loop
                insert into t1 values (lpad(to_char(i),5,'0') ,'test');
        end loop;
end;
/
commit;

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

SQL> 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
---------- ---------- ---------- ----------------------------------------------------------------
        11        382         39 leaf node splits
        11        383          0 leaf node 90-10 splits

可以發現索引節點分裂39次,而leaf node 90-10 splits=0次。

4.分析索引結構：
SQL> analyze index i_t1_a validate structure;

set linesize 200;
set linesize 200;
column name format a10
select HEIGHT, BLOCKS, NAME, LF_ROWS, LF_BLKS, LF_ROWS_LEN, LF_BLK_LEN, BR_ROWS, BR_BLKS, BR_ROWS_LEN, BR_BLK_LEN, DEL_LF_ROWS, DEL_LF_ROWS_LEN, DISTINCT_KEYS from index_stats;
select MOST_REPEATED_KEY, BTREE_SPACE, USED_SPACE, PCT_USED, ROWS_PER_KEY, BLKS_GETS_PER_ACCESS, PRE_ROWS, PRE_ROWS_LEN, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE from index_stats;


    HEIGHT     BLOCKS NAME          LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- -------------
         2         48 I_T1_A          10001         40      160016       7996         39          1         401       8028           0               0         10001


MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
                1      327868     160417         49            1                    3          0            0              0                0


可以發現PCT_USED=49,索引空間的利用率很低。

可以說明如果應用中某個索引鍵值正常業務時是線性增加的,比如順序號,或者日期等,如果表中存在某個異常數值,這樣索引空間的利用率會很低,因為由於插入資料的索引鍵值都是正常業務的"最大值",每次插入都是在索引一端,而每次都比異常數值小,這樣索引的分裂都是50－50的情況。

這種情況最容易出現一些日期欄位,因為如果應用業務的操作人員不小心輸入錯誤,輸入2032年等等情況,而這些垃圾資料沒有清除,這樣很容易出現這種情況.如果索引重整,不消除這些垃圾資料，以後的情況依舊大部分是50-50分裂。