经常DML操作对索引的影响

当表里的数据发生DML操作时，oracle会自动维护索引树。但是在索引树中没有更新操作，只有删除和插入操作。例如在某列上

创建索引，将某列上的一个值“1”更新为“2”时，oracle会同时更新索引树，但是oracle是先将索引树中的“1”标示为删除

，然后再将“2”写到索引树中。所以如果表更新比较频繁，那么在索引中删除的标示会越来越多，这时索引的查询效率必然降

低，所以我们应该定期重建索引。

create table tt (id int,name varchar2(10));

begin
    for i in 1..1000 loop
    insert into tt values(i,'aaaa');
    commit;
    end loop;
 end;
    /

create index id_inx on tt(id);

desc index_stats;

select name,lf_rows,br_rows,pre_rows,del_lf_rows from index_stats where name='ID_INX';
未选定行

--此时字典表中还没有索引的信息，要分析一下索引
SQL> analyze index id_inx validate structure;
索引已分析

select name,lf_rows,br_rows,pre_rows,del_lf_rows from index_stats where name='ID_INX';
NAME                              LF_ROWS    BR_ROWS   PRE_ROWS DEL_LF_ROWS
------------------------------ ---------- ---------- ---------- -----------
ID_INX                               1000          2          0           0

--更新索引列
SQL> update tt set id=id+1 where id<101;
已更新100行。

commit;

 

SQL> select name,lf_rows,br_rows,pre_rows,del_lf_rows from index_stats where name='ID_INX';
NAME                              LF_ROWS    BR_ROWS   PRE_ROWS DEL_LF_ROWS
------------------------------ ---------- ---------- ---------- -----------
ID_INX                               1000          2          0           0
已选择 1 行。

可以看出，进行UPDATE后，索引的有关信息并没有写入到字典表中，必须再次分析一下表
SQL> analyze index id_inx validate structure;
索引已分析

 

SQL> select name,lf_rows,br_rows,pre_rows,del_lf_rows from index_stats where name='ID_INX';
NAME                              LF_ROWS    BR_ROWS   PRE_ROWS DEL_LF_ROWS
------------------------------ ---------- ---------- ---------- -----------
ID_INX                               1100          3          0         100

SQL> update tt set id=id+1 where id>200;

已更新800行。

SQL>
SQL> commit;

提交完成。

SQL> analyze index id_inx validate structure;

索引已分析

SQL> select name,lf_rows,br_rows,pre_rows,del_lf_rows from index_stats where name='ID_INX';

NAME                              LF_ROWS    BR_ROWS   PRE_ROWS DEL_LF_ROWS
------------------------------ ---------- ---------- ---------- -----------
ID_INX                               1900          4          0         900

 

可以看到，此时标识为被删除的行数为900行，通过REBUILD可以消除。
SQL> alter index id_inx rebuild;
索引已更改。
SQL> analyze index id_inx validate structure;
索引已分析
SQL> select name,lf_rows,br_rows,pre_rows,del_lf_rows from index_stats where name='ID_INX';
NAME                              LF_ROWS    BR_ROWS   PRE_ROWS DEL_LF_ROWS
------------------------------ ---------- ---------- ---------- -----------
ID_INX                               1000          2          0           0
已选择 1 行。