Oracle Index Rebuild and Coalesce with ALTER INDEX

calendar Jun 6, 2026 / · 6 min read · ALTER INDEX Index Rebuild Index Coalesce Index Maintenance Oracle Structure Oracle DBA Scripts  ·
Share on: linkedin copy

Oracle Index Rebuild and Coalesce with ALTER INDEX

Purpose

Over months of heavy delete and update activity, a B-tree index can accumulate empty and half-full leaf blocks. The index keeps working, but it grows wider than the data it points to, so range scans read more blocks than they need and the segment holds space that is no longer doing useful work. ALTER INDEX ... REBUILD and ALTER INDEX ... COALESCE are the two commands that address this — one rebuilds the index from scratch, the other merges adjacent sparse leaf blocks in place.

Choosing between them matters. A rebuild produces the most compact possible index but is the heavier operation; a coalesce is lighter and fully online but only consolidates what is already adjacent. This post shows the syntax for both, the online and offline rebuild variants, and how to decide which one a given index actually needs. The goal is to run the right maintenance, not to rebuild every index on a schedule — a habit that costs I/O and redo for little benefit on most indexes.

Code

 1-- Rebuild an index (offline: locks the base table for DML)
 2ALTER INDEX hr.emp_dept_ix REBUILD;
 3
 4-- Rebuild online: base table stays available for DML during the rebuild
 5ALTER INDEX hr.emp_dept_ix REBUILD ONLINE;
 6
 7-- Rebuild into a different tablespace and set storage in one step
 8ALTER INDEX hr.emp_dept_ix REBUILD ONLINE TABLESPACE indx_ts;
 9
10-- Coalesce: merge sparse adjacent leaf blocks in place, fully online
11ALTER INDEX hr.emp_dept_ix COALESCE;
12
13-- Verify structure after maintenance
14ANALYZE INDEX hr.emp_dept_ix VALIDATE STRUCTURE;
15SELECT name, height, lf_blks, del_lf_rows, used_space, pct_used
16FROM   index_stats;

Code Breakdown

REBUILD

  • ALTER INDEX ... REBUILD — builds a brand-new index segment from the existing one, then drops the old segment. The result is densely packed with no empty leaf blocks. The plain form locks the base table against DML for the duration, so it suits a maintenance window.
  • REBUILD ONLINE — performs the rebuild while allowing concurrent inserts, updates, and deletes on the base table. Oracle tracks the changes made during the build and applies them at the end. This is the form to use on a live system, at the cost of more work and a longer run.
  • REBUILD ... TABLESPACE indx_ts — relocates the index to a different tablespace as part of the rebuild. This is the standard way to move an index off an over-full or mis-placed tablespace without dropping and recreating it.

COALESCE

  • ALTER INDEX ... COALESCE — walks the leaf blocks and merges the contents of adjacent, partly empty blocks so freed blocks return to the index's free list. It never locks the table, never needs extra space for a second segment, and never moves the index. It does not reduce the index height or release space back to the tablespace — it only tidies within the existing segment.

Verification

  • ANALYZE INDEX ... VALIDATE STRUCTURE — populates the session-private INDEX_STATS view with structural figures: height, leaf-block count, deleted-leaf-row count, and pct_used. A high del_lf_rows relative to total rows, or a low pct_used, indicates an index that genuinely carries dead space.

Key Points

  • A rebuild reclaims the most space and can lower the index height, but it needs room for a second copy of the index while it runs and generates significant redo. A coalesce needs no extra space and far less redo, but only consolidates adjacent blocks.
  • REBUILD ONLINE still briefly locks the table at the start and end of the operation to set up and finalize. On a very busy table even those brief locks can queue behind long transactions, so schedule with care.
  • Most indexes never need rebuilding. A B-tree index largely self-balances, and reused leaf space is the normal case. Rebuild in response to measured bloat (INDEX_STATS, segment size versus data volume), not on a blanket calendar.
  • After a large bulk delete that removed a contiguous range of key values, a coalesce or rebuild can genuinely help, because the emptied leaf blocks may not be reused if the deleted key range will not be re-inserted.
  • In Enterprise Edition, VALIDATE STRUCTURE ONLINE collects similar figures without the table lock that the plain ANALYZE ... VALIDATE STRUCTURE takes.

Insights and Best Practices

Measure before you rebuild

Do not rebuild an index because it is old. Use ANALYZE INDEX ... VALIDATE STRUCTURE (or compare the index segment size in DBA_SEGMENTS against the underlying data) to confirm real bloat first. A common rule of thumb is to consider action when deleted leaf rows exceed roughly 20 percent of total rows, but treat that as a prompt to look closer, not an automatic trigger.

Prefer coalesce for routine tidy-ups

When an index has scattered free space but is otherwise fine, COALESCE is the lower-impact choice: online, no extra space, low redo. Reserve REBUILD for cases where you need to reduce height, relocate the index, or change its storage attributes — situations a coalesce cannot address.

Always rebuild online on production

On a live database, default to REBUILD ONLINE so application DML is not blocked for the length of the build. Keep the offline REBUILD for maintenance windows where you have an outage and want the fastest, lowest-overhead path.

Watch redo and undo on large indexes

Rebuilding a multi-gigabyte index generates a large volume of redo and can pressure the FRA and the standby apply rate in a Data Guard configuration. For very large indexes, consider NOLOGGING only if you understand the recovery implications and will take a fresh backup afterward, since NOLOGGING changes are not recoverable from the redo stream.

When to Run This

  • After a large, one-off bulk delete that emptied a contiguous range of index keys.
  • When INDEX_STATS or segment-size comparison shows measurable dead space in a heavily scanned index.
  • When relocating an index to a different tablespace or changing its storage attributes.
  • As targeted maintenance on a small set of demonstrably bloated indexes — not as a blanket scheduled job.

Troubleshooting Common Issues

If REBUILD fails with ORA-01652: unable to extend temp segment, the operation ran out of space for the second copy of the index — either free space in the target tablespace or sort/temp space; check both before retrying. If REBUILD ONLINE appears to hang at the start or end, it is most likely waiting on a brief table-level lock held up by a long-running transaction; identify the blocker through V$SESSION.blocking_session and let it finish. If a coalesce seems to free nothing, the index's empty blocks were not adjacent, so there was nothing to merge — that is expected, not a fault. After any rebuild, confirm the operation actually helped by re-running VALIDATE STRUCTURE and comparing pct_used and height against the before figures, rather than assuming improvement.

References

Posts in this series