Rebuilding an Oracle Index

An index is a storage structure that accelerates row access by maintaining an ordered (or otherwise optimized) representation of column values and their corresponding ROWID locations. Over time, normal DML activity (inserts, updates, deletes) can reduce index efficiency: leaf blocks may become sparsely populated, splits may increase the number of blocks, and some access paths may require additional logical I/O to traverse the index.

When an index accumulates fragmented leaf space, Oracle may need to read more leaf blocks than necessary for range scans, and the index segment can grow larger than its effective payload warrants. In extreme cases, excessive growth can also increase the B-tree height (additional branch levels), adding extra block gets per index probe.

Rebuilding an Index

A rebuild recreates the index structure and repacks leaf blocks more densely. In Oracle 23ai, you rebuild an index with ALTER INDEX ... REBUILD:

ALTER INDEX bid_auction_idx REBUILD;

A rebuild is a full reorganization of the index segment. It can:

• Repack leaf blocks to reduce sparsity and fragmentation.
• Potentially reduce B-tree height if the index can be represented with fewer levels.
• Move the index to a new segment format (and optionally to a different tablespace) depending on the rebuild options used.

In operational terms, rebuilding is most appropriate when you need a full re-layout of the index segment (for example, after sustained churn, after large bulk deletes, or when you are deliberately changing index physical attributes such as tablespace placement or storage parameters).

Coalescing Leaf Blocks

Rebuilding is not the only way to improve index storage layout. Oracle also supports coalescing leaf blocks using ALTER INDEX ... COALESCE. Coalescing focuses on the leaf level: it attempts to merge adjacent leaf blocks so that free space is consolidated, while leaving branch blocks unchanged.

ALTER INDEX bid_auction_idx COALESCE;

Coalescing is typically used when the index is structurally sound, but leaf-level fragmentation has increased due to deletes and leaf block splits. Because it does not rebuild the full tree, coalescing is generally lighter-weight than a rebuild and is often chosen when you want incremental improvement without a full segment reorganization.

Examine the following diagrams. Figure 5-1 shows a B-tree index before coalescing, where multiple leaf blocks are only partially populated. Figure 5-2 shows the same index after coalescing, where adjacent leaf blocks have been merged so that the first leaf blocks are filled more efficiently and an empty leaf block can be released for reuse.

Some indexes become inefficient for a different reason: insert activity concentrates on one edge of the B-tree. A common example is an index on a monotonically increasing key (such as a sequence-generated identifier). New keys are inserted near the right-most leaf block, which can increase block split frequency and, in clustered or multi-instance environments, can increase contention for the same leaf blocks.

Oracle can mitigate this “right-hand growth” pattern using a reverse key index. With a reverse key index, Oracle reverses the byte order of the indexed key during storage so that sequential values are distributed more evenly across the leaf blocks. For example, increasing values such as 234, 235, and 236 are stored using reversed byte patterns, which disperses inserts throughout the index structure rather than concentrating them at a single hot leaf block.

Reverse key indexes can reduce hot-block contention, especially in Oracle Real Application Clusters (Oracle RAC), where multiple instances may otherwise repeatedly modify the same right-most leaf block. However, because the stored key order is not logically ordered for range semantics, reverse key indexes are not appropriate for workloads that require efficient index range scans on the reversed key.

A reverse key index is a type of B-tree index that reverses the bytes of each indexed value while keeping the column order intact. This technique reduces leaf-block contention on sequential inserts by distributing new entries across many leaf blocks.

Consider a scenario with sequential primary keys in an orders table. Without reversing, keys such as 20 and 21 would typically be inserted into adjacent entries near the right edge of the index. In a reverse key index, those keys are stored using reversed byte order and are therefore not adjacent in the leaf structure. Inserts become more evenly distributed, reducing the likelihood that multiple sessions (or multiple RAC instances) repeatedly target the same leaf block.

Because the stored values are not ordered in the original key sequence, some access patterns are limited. In particular, queries that rely on a contiguous range of values (for example, “order_id > 20”) cannot perform a conventional index range scan by walking leaf blocks in key order.

Oracle can reuse space within an index block as index entries are inserted and deleted. Individual index slots may be reused when new rows require space. However, an index block itself is only considered free when it becomes empty. When a block becomes empty, Oracle can place it on the free list for the index and make it available for reuse.

Oracle Database does not automatically compact a fragmented index segment into fewer blocks. To consolidate free space and improve storage efficiency, you must explicitly reorganize the index using ALTER INDEX ... REBUILD or ALTER INDEX ... COALESCE.

Figure 5-1 represents an index of the employees.department_id column before coalescing. Several leaf blocks are only partially full. Figure 5-2 shows the same index after coalescing, where adjacent leaf blocks have been merged and empty space has been consolidated.

Click the Quiz link below to answer a few questions about rebuilding and coalescing indexes.

Altering Indexes - Quiz

The next lesson shows how to query the data dictionary for index metadata and storage details.