Rebuild ? There is an alternative

Search the newsgroup or FAQ sites and there is no end of tools for diagnosing when to consider rebuilding indexes (typically using either normal index statistics or those from INDEX_STATS) so I won't dwell on them here.

Similarly, you can check my import speed tip for some hints on how to speed up the (re-)building of an index. However, from 8i onwards, there is another option you may wish to consider. Its fully documented in the standard Oracle documentation - but its worthy of mention here, because it has not been widely publicised.

ALTER INDEX vmoore COALESCE;

Coalescing an index, de-fragments the leaf blocks for an index as opposed to a full rebuild cycle. In many instances, this may be sufficient remedial action.

Positives

• Less resource used - in fact no additional space is required
• Faster (typically)
• Good for databases with large block sizes (see the negative point below on index height)

Negatives

• You aren't recreating anything, so obviously you cannot move the index or adjust its storage parameters during a coalesce
• Since only the leaf blocks are coalesced, the height of the index tree will not be changed. For databases with larger block sizes, this should be less significant since the indexes will be "flatter" anyway.