索引原理

当我们创建索引时，oracle会为索引创建索引树，表和索引树通过rowid(伪列)来定位数据。当表里的数据发生更新时，oracle会自动维护索引树。但是在索引树中没有更新操作，只有删除和插入操作。例如在某表id列上创建索引，某表id列上有值“101”，当我将“101”更新为“110”时，oracle同时会来更新索引树，但是oracle先将索引树中的“101”标示为删除（实际并未删除，只是标示一下），然后再将“110”写到索引树中。

 

如果表更新比较频繁，那么在索引中删除标示会越来越多，这时索引的查询效率必然降低，所以我们应该定期重建索引。来消除索引中这些删除标记。

一般不会选择先删除索引，然后再重新创建索引，而是rebuild索引。在rebuild期间，用户还可以使用原来的索引，并且rebuild新的索引时也会利用原来的索引信息，这样重建索引会块一些。

这个实验来察看索引中的删除标记，并且如何重建索引。

试验环境：oracle 8.1.7

SQL> create or replace procedure sp_insert_ind
 is

begin

for i in 1..10000 loop

 insert into ind values(i,to_char(i)||'aaaaaaaaaa');

end loop;

end;

/

过程已创建。

SQL> exec sp_insert_ind

PL/SQL 过程已成功完成。

SQL> create index ind_id_idx on ind(id);

索引已创建。

 

 

二、收集索引信息

-收集信息，没有更新数据字典，所以没有信息

SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

未选定行

--更新数据字典

SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;

 

索引已分析

--参数含义：

--LF_ROWS Number of values currently in the index

--LF_ROWS_LEN Sum in bytes of the length of all values

--DEL_LF_ROWS Number of values deleted from the index

--DEL_LF_ROWS_LEN Length of all deleted values

 

 

 

SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats

 LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN

 10000      149801           0               0

--察看索引中已经标示为删除的行除以总共的行的数量，目前为0

SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage  FROM index_stats;

INDEX_USAGE    

------

0

三、更新索引，并且重新察看信息

 

--更新表中1000行记录，这时会更新索引树
SQL> update ind set id=id+1 where id>9000;

已更新1000行。

 

SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;

索引已分析

--总共行的数量增加了1000行，并且标示为删除了1000行记录

SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

 LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN

 11000      164792        1000           14990

--察看索引中已经标示为删除的行除以总共的行的数量，目前为 9.09631536，这个值如果查过20，肯定要重建索引了。

SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage  FROM index_stats;

SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage  FROM index_stats;

INDEX_USAGE  

--------------------

9.09631536

本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/ONLYONE_HTLIU/archive/2010/10/09/5930051.aspx

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25088941/viewspace-681851/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25088941/viewspace-681851/