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或者
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;