When to rebuild an Index .

We will follow the below procedure to find which index to rebuild.

analyze index INDEXMANE validate structure;

Once it is complete run the below three queries

Select del_lf_rows / lf_rows from index_stats;
Select HEIGHT from index_stats;
Select LF_ROWS, LF_BLKS from index_stats;

Now an index should be rebuilt if
1) percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3.
2) If the ‘HEIGHT’ is greater than 4.
3) If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.

For EXample

1)

INDEX_NAME: WF_ITEM_ATTRIBUTE_VALUES_PK
Size in MB:2000m
Del%(EL_LF_ROWS/LF_ROWS)%:17
HEIGHT: 4
LF_ROWS:31399384
LF_BLKS:265071
REBUILD: NO

2)

INDEX_NAME: CCIX_PLSQL_LOG_N1
Size in MB:1500
Del%(EL_LF_ROWS/LF_ROWS)%:0
HEIGHT: 4
LF_ROWS:57220
LF_BLKS:164476
REBUILD: YES

As LF_BLKS: is considerably higher thn LF_ROWS condition number 3.

Eg 3:

INDEX_NAME: CCIX_PLSQL_LOG_N1
Size in MB:1500
Del%(EL_LF_ROWS/LF_ROWS)%:35
HEIGHT: 4
LF_ROWS:17674676
LF_BLKS:154250
REBUILD: YES


Del%(EL_LF_ROWS/LF_ROWS)% > 30 condition 1.

Thanks,
Sandarsh