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

Negatives