Databases Reference
In-Depth Information
scans the index leaf blocks to compare the free space in neighboring index blocks. If there's free space in
a block, the block's contents are merged with the contents of another block, thus freeing up index leaf
blocks where possible. The database removes any freed index blocks from the index structure and places
them on the free list of index blocks.
Coalescing an index keeps the space you allocated for the index intact; it doesn't return the unused
space to the database. If you have a case where you're dealing with an index with monotonically
increasing values such as on a sequence or a date and you delete a lot of the old values, coalescing might
be helpful. Many shops regularly purge older data based on the sequence number or a data range.
Coalescing indexes in such cases helps you reclaim the unused space, which is not going to be reused by
the indexes anyway. If you're performing a
select
of all the rows in a table with such an index and are
ordering the results by the indexed column, the database will have to read the mostly empty index leaf
blocks. Queries might perform better when you coalesce such an index. Coalescing rather than
rebuilding the index is the right action to take here. Unlike in the case of an index rebuild, coalescing an
index doesn't require additional disk space; rebuilding an index requires space both for the original and
the new index structures until the index is rebuilt. Coalesce also runs much faster than an index rebuild
in most cases, freeing up unused leaf blocks for reuse.
Note
Both the
coalesce
and the
shrink
commands result in the same number of leaf blocks in the index. The
index height remains unchanged, unlike in the case of an index rebuild where the index height is sometimes
shortened.
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 operation in two steps. If you specify the
compact
clause with
the
shrink space
command, the database only performs a defragmentation of the segment space. It
compacts the index but doesn't immediately release the free space. You must issue a separate
alter
index ...shrink space
command to make the index release the free space. Since compacting an index
segment may require 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—if you specified
autoallocate
for the tablespace. If you specified
uniform
extent allocation, on the
other hand, the database won't return any extent that contains even a single block of index data.
