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 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.
