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: ...

Get Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.