索引重建问题

oracle狂热分子

                                      索引重建问题

    索引叶子块在频繁的DML操作开始变得不连续起来，主要是因为删除相应的数据以后，索引slot并不会重用。

通过index_stats视图中的del_lf_rows/lf_rows*bocks可以决定，如果重减索引可以带来相应的读块数减少,以达
到减少buffer gets或physical read程度

1,创建一个表t1
SQL> create table t1 as select * from dba_objects;

Table created.

2,创建一个test表，用来存放index_stats;
SQL> create table test as select * from index_stats;

Table created.

3,在t1上分别创建索引objectid_ind和objectname_ind;
SQL> create index objectid_ind on t1(object_id);

Index created.
SQL> create index objectname_ind on t1(object_name);

Index created.

4，分析索引，并存放到test中，
declare
j varchar2(100);
m varchar2(100);
cursor i
is
select index_name from dba_indexes where table_name='T1';
begin
open i;
loop
fetch i into j;
exit when i%NOTFOUND;
m:='analyze index sys.'||j||' validate structure';
execute immediate m;
insert into test  select * from index_stats;
end loop;
close i;
end;

5，由于两索引是新建的，因此并不存在碎片问题，查询一下
SQL> select name,blocks,del_lf_rows/lf_rows*100,del_lf_rows/lf_rows*blocks from
test;

NAME                BLOCKS DEL_LF_ROWS/LF_ROWS*100 DEL_LF_ROWS/LF_ROWS*BLOCKS
--------------- ---------- ----------------------- --------------------------
OBJECTID_IND           112                       0                          0
OBJECTNAME_IND         256                       0                          0

结果显示现在索引没有碎片化的问题;

5,现在来模拟删除t1一半的数据;

SQL> delete t1 where rownum<30000;

29999 rows deleted.

6,清除test表中的数据，再次运行第4步的脚本和第5步的查询看一下；
SQL> truncate table test;

Table truncated.
SQL> select name,blocks,del_lf_rows/lf_rows*100,del_lf_rows/lf_rows*blocks from
test;

NAME                BLOCKS DEL_LF_ROWS/LF_ROWS*100 DEL_LF_ROWS/LF_ROWS*BLOCKS
--------------- ---------- ----------------------- --------------------------
OBJECTID_IND           112              31.5693289                 35.3576483
OBJECTNAME_IND         256               57.393599                 146.927613
表明索引的blocks没有减少，但是碎片程度很高了，分别是31.5%和57.3%,最后一列，表明
重建索引可以带来块的读数减少

7,分别重建索引
SQL> alter index OBJECTID_IND rebuild;

Index altered.

SQL> alter index OBJECTNAME_IND rebuild;

Index altered.

8,再次清空test,运行第4步的脚本和第5步的查询看一下；
SQL> truncate table test;

Table truncated.

SQL> select name,blocks,del_lf_rows/lf_rows*100,del_lf_rows/lf_rows*blocks from
  2  test;

NAME                BLOCKS DEL_LF_ROWS/LF_ROWS*100 DEL_LF_ROWS/LF_ROWS*BLOCKS
--------------- ---------- ----------------------- --------------------------
OBJECTID_IND            48                       0                          0
OBJECTNAME_IND          96                       0                          0
此时索引的状态又是最佳状态了.

结论:通过计算del_lf_rows/lf_rows*bocks来自行决定是否重建索引.该值占某index的
blocks应该越优先重占,我个人认为应该超过10%就应该重建了，在buffer gets很高的系统
上应该还要低一些就开始考虑重建.