Rebuild and Defragment Indexes Online in Oracle 23ai

Index maintenance is sometimes necessary after heavy DML activity (large deletes, frequent updates, bulk loads) or after structural changes (partition maintenance, segment moves, tablespace changes). Historically, major index maintenance could block or heavily disrupt concurrent DML on the base table. Modern Oracle releases support online index operations designed to keep the application available while you perform maintenance.
In Oracle 23ai, you can perform key index operations online, most notably:

1. REBUILD ONLINE to rebuild an index while allowing concurrent DML on the underlying table.
2. COALESCE (and in some cases coalesce online behavior depending on edition/features) to compact leaf blocks and reduce wasted space.

The objective of this lesson is to show where the ONLINE keyword is valid, what it accomplishes, and how to use it safely in real systems.

The most common online maintenance operation is rebuilding an existing index without taking the table offline for writes. The canonical syntax is:

ALTER INDEX index_name REBUILD ONLINE;

This operation creates a new index structure while Oracle maintains DML consistency. At cutover, Oracle switches to the rebuilt index with minimal disruption. Online rebuild is especially useful when you need to:

• Move an index to a different tablespace during rebuild (for example, storage tier changes).
• Change storage parameters (subject to syntax options and your standards).
• Recreate an index after it becomes UNUSABLE (common after certain partition operations).
• Recover performance after heavy fragmentation or leaf block inefficiency.

Oracle also supports online index creation in many configurations, allowing concurrent DML during the index build. If your environment supports it, the syntax is:

CREATE INDEX index_name ON table_name (column_list) ONLINE;

Operationally, the key difference is:

• CREATE INDEX ... ONLINE builds a new index while allowing concurrent DML.
• ALTER INDEX ... REBUILD ONLINE rebuilds an existing index while allowing concurrent DML.

Note that specific restrictions can apply depending on index type, table features, partitioning, and your licensing/edition. In production, always validate behavior in a staging environment for your exact object types and workload.

Oracle provides two common approaches to reclaim wasted space and improve index efficiency:

1. COALESCE compacts leaf blocks by merging adjacent leaf blocks and freeing empty blocks where possible. It is typically less disruptive and usually requires fewer resources than a rebuild.
2. REBUILD recreates the entire index structure. It can be more resource-intensive, but it can also deliver a “cleaner” structure, and it is often used when moving the index segment or when the index is unusable.

In many real environments, DBAs prefer to start with coalesce where it is sufficient, and reserve rebuild for cases where coalesce cannot achieve the desired result or where additional structural changes are required.

ALTER INDEX index_name COALESCE;

A practical decision framework:

• Use COALESCE when the goal is modest space cleanup and you want a lighter-weight operation.
• Use REBUILD ONLINE when you need major restructuring, tablespace relocation, or recovery from unusable state.

Before dropping or changing indexes in a production system, Oracle provides states that help with safe testing and operational control:

• INVISIBLE: Oracle maintains the index as DML occurs, but the optimizer does not consider it for query plans (unless session-level settings override this behavior). This is useful for testing whether an index is truly required for performance.
• UNUSABLE: The optimizer will not use the index and Oracle does not maintain it during DML. This is sometimes used intentionally during bulk loads or after partition operations, but it must be rebuilt before it can be used again.

ALTER INDEX addr_fk1 INVISIBLE;
ALTER INDEX addr_fk1 VISIBLE;

ALTER INDEX addr_fk1 UNUSABLE;
ALTER INDEX addr_fk1 REBUILD ONLINE;

Online operations reduce downtime risk, but they still consume resources. Plan and monitor:

• Redo/undo and temporary space: online builds can increase redo/undo generation compared to offline operations.
• Concurrency: online operations allow DML, but high write rates can prolong the rebuild and increase overhead.
• Maintenance windows: even “online” work is best scheduled during lower activity when possible.
• Validation: confirm index usability and query plan behavior after changes (especially if you toggled visibility).

Click the Exercise link below to practice rebuilding an index online.
Rebuilding Defragmented Indexes Online - Exercise