Databases Reference
In-Depth Information
This statement makes the test_idx1 index unavailable for use until the rebuild operation completes. You
can optionally rebuild an index online, as shown here:
SQL> alter index test_idx1 rebuild online;
Index altered.
SQL>
Rebuilding an index online allows the database to use the index during its rebuild, thus enhancing
availability. The online index rebuild capability, although available in earlier releases, didn't always work
as advertised. In the Oracle Database 11g release, however, an online rebuild doesn't lead to any locking
of the underlying table when you rebuild an index.
Rebuilding Reverse-Key Indexes
When you rebuild an index with the
reverse
option, the database excludes the
ROWID
and stores the bytes
of the index blocks in reverse order. For example,
SQL> alter index test_idx1 rebuild reverse;
Index altered.
SQL>
Chapter 5 explains reverse key indexes in detail. As that chapter explains, reverse key indexes are
beneficial in some specific circumstances, especially in Oracle RAC environments, but have the
drawback of not enabling the use of index range scans.
Reclaiming Unused Space
DBAs sometimes rebuild indexes in order to reclaim unused space. You can actually deallocate space
from an index by executing the
alter index …deallocate
statement. For example,
SQL> alter index test_idx1 deallocate unused;
Index altered.
SQL>
When you deallocate space like this, the Oracle database deallocates unused space
at the end
of an
index (or table) segment. Unused space within the segment caused by entries that have been deleted or
moved is not released. Only space at the end of the segment is released.
Rebuilding a Partitioned Index
Several maintenance operations on tables mark any corresponding indexes or index partitions as
invalid. If a local index partition is marked invalid, you must rebuild just the associated local index
