Index Rebuilding: The Debate
There's a fair bit of debate over the question of whether to rebuild indexes, especially on a routine basis. There are many reasons why DBAs rebuild. Some are valid; some are based upon myth or misunderstanding.
Arguments for Rebuilding
In this section, we summarize the arguments traditionally advanced to support regular rebuilding of indexes.
- Oracle B-tree indexes become unbalanced over time: One of the most common reasons advanced by proponents of frequent rebuilds is that Oracle B-tree indexes become unbalanced over time if they're subject to a heavy amount of updates and deletes. This is not true as the height between the root block and all the leaf blocks is always consistent.
- Deleted space in an index is deadwood: ...