Index Fragmentation / Rebuild

How to find index fragmented?

Step 1, Analyze the index.

 

SQL> analyze index INDEX_NAME validate structure;

Step 2, Query  INDEX_STATS  view

SQL> column status format a10
SQL> select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;

status
----------
21.83%

 

1.  If del_lf_rows/lf_rows is > .2  then index should be rebuild.
2.  If height is 4  then index should be rebuild.
3.  If lf_rows is lower than lf_blks  then index should be rebuild.

 

How to remove index fragmentation?

There are two options to remove fragmentation.

1. index coalesce
2. index rebuild

What is difference between coalesce and rebuild please go through below link for more details.

http://download.oracle.com/docs/cd/B14117_01/server.101/b10739/indexes.htm#g1007548

SQL> alter index IDX_OBJ_ID coalesce;

SQL> alter index IDX_OBJ_ID rebuild;

SQL> alter index IDX_OBJ_ID rebuild online;

 

Note: If any DML statement is running on base table then we have to use ONLINE keyword with index rebuilding.

 

举个例子:

SQL> analyze index idx_obj_id validate structure;

Index analyzed.

SQL> select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;

status
-------
40.85%

SQL> alter index IDX_OBJ_ID rebuild online;

Index altered.

SQL> analyze index idx_obj_id validate structure;

Index analyzed.

SQL> select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;

status
--------
0%

Note: Index rebuild when index is fragmented or it is needed, otherwise index rebuilding is myth for improve performance.

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

转载于:http://blog.itpub.net/22473175/viewspace-614459/