1 If the ratio of deleted to actual leaf entries is more than 20% or
2 If the "blevel">3 (in table dba_indexes)
Here's how you can find it.You are supposed to do
and then query the view INDEX_STATS
SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 FROM INDEX_STATS;
Here's the command to rebuild indexes ->
You will require twice the space because during index rebuilding, the old index will still be there and will be dropped only when the new one has been created successfully.
References-> Oracle metalink document Id-182699.1 when and how to rebuild indexes. It provides the "bde_rebuild" script to automate rebuilding indexes .