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 '%';

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 '%';

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

How to Determine When an Index Should be Rebuilt?

APPLIES TO:

GOAL

SOLUTION

相關文章