Index Rebuild condition

Every so often, we need to rebuild indexes in Oracle, because indexes become fragmented over time. This causes their performance - and by extension - that of your database queries, to degrade. 

There are 2 rules of thumb to help determine if the index needs to be rebuilt:

1. If the index has height greater than four, rebuild the index.
   
   
2. The deleted leaf rows should be less than 20%.
   
   

3. To re-create an existing index or one of its partitions or subpartitions. If the index is marked unusable, then a successful rebuild will mark it usable.

4. 
   

ALTER INDEX [schema.]index REBUILD  /ONLINE / PARTITION partition