9i index bug.txt

1.建立表以及索引
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL> show rel
release 902000800

create table t1 (a number,b varchar2(10));
create table t2 (a number,b varchar2(10));
create table t3 (a number,b varchar2(10));
create unique index i_t1_a on t1(a);
create unique index i_t2_a on t2(a);
create unique index i_t3_a on t3(a);
--三个表结构完全一样!

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
--------- ---------- ---------- ------------------------------
      103        201          0 leaf node splits
      103        202          0 leaf node 90-10 splits


2.插入数据到t1(采用批量插入的模式).

insert into t1  select rownum,'test' from dual connect by level<=10000;
或者
begin
        for i in 1..10000         loop
                insert into t1 values (i,'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%';
       SID STATISTIC#      VALUE NAME
---------- ---------- ---------- ------------------------------
       103        201         17 leaf node splits
       103        202         17 leaf node 90-10 splits

analyze index i_t1_a validate structure;
@i
    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         24 I_T1_A          10000         18      139801       8000         17          1         170       8032           0               0         10000


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      152032     139971         93            1                    3          0            0              0                0

    从这里可以验证索引分裂了17次,每次都是leaf node 90-10 splits.pct_user=93,LF_BLKS=18 .

3.插入数据到t2(采用当次提交的模式,就是插入一条提交一次！),退出再进入,便于观察:
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
--------- ---------- ---------- ----------------------------
      103        201          0 leaf node splits
      103        202          0 leaf node 90-10 splits

begin
        for i in 1..10000         loop
                insert into t2 values (i,'test');
                commit;
        end loop;
end;
/

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
---------- ---------- ---------- ------------------------------
       103        201         33 leaf node splits
       103        202          0 leaf node 90-10 splits

--两者不同是一个批量插入,一个插入一条commit一条.

analyze index i_t2_a validate structure;
@i

    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         40 I_T2_A          10000         34      139801       8000         33          1         330       8032           0               0         10000


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      280032     140131         51            1                    3          0            0              0                0

    从这里可以验证索引分裂了33次,而每次都不是leaf node 90-10 splits（leaf node 90-10 splits=0）.pct_user=51,LF_BLKS=34 .
    前后对比发现,后面的方式索引的使用很低,index split都是50-50 splits.导致索引占用空间很大.

4.插入数据到t3(采用每次提交5条记录的方式！),退出再进入,便于观察:

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
--------- ---------- ---------- ------------------------------
      103        201          0 leaf node splits
      103        202          0 leaf node 90-10 splits

begin
        for i in 1..10000         loop
                insert into t3 values (i,'test');
                if (mod(i,5) =0) then
                      commit;
                end if;
        end loop;
end;
/

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
---------- ---------- ---------- ------------------------------
       103        201         31 leaf node splits
       103        202          2 leaf node 90-10 splits

analyze index i_t3_a validate structure;
@i
    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         40 I_T3_A          10000         32      139801       8000         31          1         307       8032           0               0         10000


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      264032     140108         54            1                    3          0            0              0                0

    从结果看索引分裂31次,仅仅leaf node 90-10 splits=2次.pct_user=54,LF_BLKS=32 .
    仅仅比每次提交的情况好一点.可以测试每次插入N条的各种情况,有些好一些,有一些差一点.
    

5.结论：
    一般正常业务oltp,如果不是做多条记录的插入,而索引又是使用序列号,日期等做主键,而这些主键基本都是顺序递增的情况下,这样会导致这些索引占用空间很大,也许正是这个原因,导致许多人存在一个误区,索引要经常性rebuild,实际上以上测试在10g上不存在!


附录.查询index_stats的脚本i.sql
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;