oracle 9i index bug?

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;