Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database

Coalescing Indexes to Reduce Fragmentation

The coalesce command tells the database to merge the contents of the index blocks to free blocks for reuse later, where it is possible to do so. Here's an example:

SQL> alter index test_idx1 coalesce; Index altered. SQL>

Coalescing an index doesn't release space back to the database. The purpose of the coalesce command is to reduce fragmentation in an index. It doesn't deallocate space that has been allocated to an index segment. Coalescing an index performs an in-place reorganization of the index data. It combines adjacent leaf blocks into a single leaf block and puts the newly empty leaf blocks on the free list of the index segment. The freed up index leaf blocks are reused by the database during ...