How to reduce fragmentation in an index

posted May 2, 2012, 5:50 AM by Sachchida Ojha   [ updated May 2, 2012, 5:51 AM ]
There are 2 ways to reduce the fragmentation in an index. 1. Coalescing index  2. Shrinking index

1. The coalesce command tell the database to merge the contents of the index blocks to free free blocks for reuse later, where it is possible to do so. For example

SQL> alter index test_idx1 coalesce;

Index altered.

Coalescing index does not release space back to the database.

The purpose of the coalesce command to reduce fragmentation in an index. It combines adjacent leaf blocks into a single leaf block and puts the newly empty leaf blocks on the free list of index segment.

Instead of coalescing an index, you can shrink an index segment using shrink command.

SQL> alter index test_idx1 shrink space;

Index altered.

The advantage of shrink space command is that it compacts the index segment and the database will immediately release any space that has been freed up.

You can specify this command for table, index, index partitions/subpartitions also.

For large index, oracle might take long time to complete the shrink operation therefore oracle lets you do this operation in 2 steps.

1. SQL> alter index test_idx1 shrink space compact;  -> Only performs defragmentation of the segment space.
2. SQL> alter index test_idx1 shrink space;  --> Release the free space.

Since shrinking an index segment requires the database to perform ROW MOVEMENT, you must first enable row movement for a table before you specify the compact clause.

When you shrink an index, the database returns all the freed up space to the tablespace holding the index provided you have specified AUTOALLOCATE for the tablespace. if you specified UNIFORM extent allocation, the database won't return any extent that contains even a single block of index data.