Shrinking Indexes to Reduce Fragmentation

Instead of coalescing an index, you can shrink an index segment by specifying the shrink space clause as shown here:

SQL> alter index test_idx1 shrink space; Index altered. SQL>

Shrinking an index compacts the index segment and the database will immediately release any space that has been freed up. You can specify the shrink space clause to reduce space usage in not only an index, or index partition/subpartition, but also in a table. For the primary keys of an index organized table, you must use the alter table statement instead and specify the coalesce clause.

For a large index, the database may take quite a bit of time to complete the shrink operation. Therefore, Oracle lets you perform a shrink ...

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

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.