Managing Index Space Usage

Over time, indexes can potentially experience fragmentation due to large numbers of deletions (as well as some types of insertions). DBAs often wonder about the correct approach to handling the space usage by indexes, especially large ones. Oracle provides three ways to handle fragmentation within an index-rebuilding, coalescing, and shrinking an index—and each of them serves a different purpose. The following sections discuss three index reorganization techniques and provide some insight into when a certain approach is appropriate and when it isn't.

Rebuilding to Reduce Fragmentation

Rebuilding an index recreates an existing index. You can rebuild an entire index, a partition, or a subpartition of a partitioned index ...

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

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.