ITPub博客

首页 > 数据库 > Oracle > How to Determine When an Index Should be Rebuilt?

How to Determine When an Index Should be Rebuilt?

Oracle 作者:lwitpub 时间:2015-02-04 17:51:39 0 删除 编辑

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.7.3 to 11.2.0.3 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
< Checked for currency 24-Sep-2014>

GOAL

How to determine when an index should be rebuilt?

NOTE:
Please note that the reason to rebuild an index should be because of poor performance of your queries using the index. 
You should/must not rebuild indexes if you find the results for both relevant queries in 1. and 2. true for an index but that is not accompanied by poor SQL performance.

For a more current approach, please refer to
Note 989093.1 - Index Rebuild, the Need vs the Implications

This Note 1373415.1 has been archived in favor of Note 989093.1 

SOLUTION

1- Find indexes having height(blevel+1) > 4

i.e. Indexes having BLEVEL > 3

SQL> select owner, index_name, table_name, blevel from dba_indexes where BLEVEL>3


2- Analyze indexes to find ratio of (DEL_LF_ROWS/LF_ROWS*100) is > 20 by "analyzing the index with validate structure option" and then: 

SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;


As already stated, the blevel is not always an indication for a bad index. Please refer to Note 989093.1  
However, rebuilding an index may help performance in specific cases.

See this example:

SQL> analyze index TEST_INDX validate structure; -- First analyze the suspect index

Index analyzed.

SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;

NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS
------------ ---------- ---------- ----------- -------------
TEST_INDX 8 938752 29575 73342


You can see height of the index is 8 and also high number of DEL_LF_ROWS.

SQL> set autotrace on
SQL> set timing on
SQL>
SQL> select count(*) from TEST_TABLE where TEST_COL like 'http://www.hots%';

COUNT(*)
----------
39700
Elapsed: 00:00:27.25

Execution Plan
----------------------------------------------------------
Plan hash value: 870163320


Id Operation Name Rows Bytes Cost (%CPU) Time


0 SELECT STATEMENT 1 117 10 (0) 00:00:01

1 SORT AGGREGATE 1 117

*2 INDEX RANGE SCAN TEST_INDX 115 13455 10 (0) 00:00:01

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
764 consistent gets
757 physical reads

0 redo size
516 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Now you rebuild the indexes

SQL> alter index TEST_INDX rebuild;

Index altered.

SQL> select count(*) from TEST_TABLE where TEST_COL like 'http://www.hots%';

COUNT(*)
----------
39700

Elapsed: 00:00:06.18

Execution Plan
----------------------------------------------------------
Plan hash value: 870163320 - See here although it is using the same plan but still it is faster


Id Operation Name Rows Bytes Cost (%CPU) Time

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

0 SELECT STATEMENT 1 117 6 (0) 00:00:01

1 SORT AGGREGATE 1 117

* 2 INDEX RANGE SCAN TEST_INDX 115 13455 6 (0) 00:00:01


Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
592 consistent gets
588 physical reads

0 redo size
516 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)


SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows,distinct_keys,used_space FROM INDEX_STATS;

NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS
------------------------------ ---------- ---------- ---------- -----------
TEST_INDX 4 865410 15434 0


This clearly indicates the rebuilt OF THE INDEX helped the query performance in this particular situation.
The height of index is reduced to 4 and DEL_LF_ROWS is 0

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

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-05-08

  • 博文量
    107
  • 访问量
    395481