When to rebuild Indexes in Oracle

Generally indexes in oracle are rebuilt more often than they should really indeed be. Richard Foote the famous expert in Oracle Indexes are not in favor of index rebuilds. Oracle Indexes are actually B+ Tree implementation so they should almost be balanced perfectly even with large number of deletes. And those deleted blocks or blocks not fully deleted but with some deleted entries can reuse the space for the new index keys so not rebuilding indexes saves expensive rebuild operation which require outage in oracle standard edition. If index has been rebuilt and space is more tight in blocks then later DMLs can be slow as they may required index block split. So traditional approach to query index_stats and deciision to rebuild index based on deleted leaf rows/blocks is no longer prudent in most of scenarios.

 Following are  Metalink notes id about Index rebuilding, these have similar thought as Richard.

122008.1

Index Rebuild, the Need vs the Implications [ID 989093.1]

Script to investigate a b-tree index structure [ID 989186.1]

Validates and rebuilds indexes occupying more space than needed  [182699.1]

click here interesting discussion in Oracle forum for index rebuild