Databases Reference
In-Depth Information
Even in a case with an index structure with a lot of brown leaves, most range scans will still run fine. Only
those range scans that start from the far left of the index (those that query the earliest data) will
deteriorate in performance due to the deletion of the index entries. In any case, don't blindly rebuild
indexes on a regular basis as part of a "database maintenance" effort. You may end up with minimal
gains for all the extra work. If you determine the root cause of a performance issue is that an index needs
rebuilding, rebuild the index—just don't schedule automatic index rebuilds based on the wrong
criterion (
DEL_LF_ROWS
). If your
before and after
performance measurements show improvements (say, in
reducing the number of index block reads), the rebuild helped; otherwise it didn't.
We can, however, see several specific situations where a DBA may rightly rebuild an index. Here are
some of the most common scenarios:
•
If an index or an index partition is damaged by media failure, index building may
be the only alternative in some cases.
Rebuild index partitions that have been marked
UNUSABLE
.
•
•
Rebuild indexes if you want to quickly move them to a different tablespace, or if
you want to change certain storage parameters.
•
Rebuild an index partition following a data load of the table partition with
SQL*Loader utility.
•
Rebuild an index to enable key compression.
•
Unlike B-tree indexes, a bitmap index can grow very large very quickly and may
benefit from a rebuild.
Instead of rebuilding an index, you can shrink the space used by an index by coalescing or shrinking
an index. Using the
alter index ...shrink space compact
command gets you the same results as when
you execute the
alter index...coalesce
command. Both the
shrink
and
coalesce
commands are
alternative ways to compact an index segment. Both operations achieve the same purpose and are in
essence identical, but the
shrink
command offers more options, as explained in the following sections.
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 subsequent
block splits. The goal here is to reduce the free space within the leaf blocks of an index. The database
