ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Index Fragmentation / Rebuild

Index Fragmentation / Rebuild

原创 Linux操作系统 作者:annnnna 时间:2009-09-11 15:51:09 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-09-04

  • 博文量
    23
  • 访问量
    12520