何时重建index

 

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-deleted entries represent 20% or more of the current entries. 

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;


 

And possible candidate for bitmap index were considered when the distinctiveness was more than 99%.

*** Please note that the reason to rebuild should be because of poor performance of your queries using indexes. You should/must not rebuild indexes if you find both the above reason true for index if it is not coupled with poor SQL performance.