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分裂。