Rebuilding Local Index Partitions in Oracle

For index partitions to remain effective, they must be properly maintained. Index partitions can become unbalanced or fragmented over time due to INSERT, UPDATE, and DELETE activity, or can be marked UNUSABLE by partition maintenance operations on the underlying table. Oracle provides two mechanisms for index maintenance: full index rebuild and single-partition rebuild. Individual partition rebuilds can run concurrently — one partition rebuilding while others continue to service SQL queries.

Do not rebuild index partitions unless there is a solid reason to do so. Rebuilding is generally unnecessary for healthy indexes. Valid reasons to rebuild include:

1. An index partition is corrupt due to media failure or software corruption of the segment containing the partition.
2. The index partition must be moved to a different tablespace.
3. An index partition has been marked UNUSABLE by a partition maintenance operation (DROP, SPLIT, MERGE, or MOVE) on the underlying table.
4. An index partition was bypassed during a bulk data load using Import or SQL*Loader and must be rebuilt after the load completes.

The capability to rebuild only part of an index is a significant performance feature. Only the partition that is out of balance needs to be rebuilt. The remaining partitions continue to service queries throughout the rebuild operation.

The full index rebuild command recreates the entire index tree. Use the ONLINE clause in production environments to keep the index usable throughout the rebuild:

-- Rebuild entire index (index locked during rebuild)
ALTER INDEX index_name REBUILD;

-- Rebuild online — index remains usable during rebuild (preferred for production)
ALTER INDEX index_name REBUILD ONLINE;

Before rebuilding, analyze index usage and fragmentation level. Rebuilding is resource-intensive — it requires additional disk space for temporary segments and CPU time proportional to the index size. Consider alternatives first: query plan analysis, SQL optimization, and database parameter tuning. Reserve index rebuilds for the four conditions listed above.

ALTER INDEX REBUILD PARTITION regenerates a single partition in a local or global partitioned index without affecting any other partition. This eliminates the need to DROP and recreate the entire index, which would take all partitions offline simultaneously.

-- Rebuild a single index partition
ALTER INDEX index_name REBUILD PARTITION partition_name
TABLESPACE tablespace_name;

-- Rebuild a single index partition online (partition remains usable)
ALTER INDEX index_name REBUILD PARTITION partition_name
TABLESPACE tablespace_name ONLINE;

Always specify the TABLESPACE clause when rebuilding a partition. If omitted, Oracle rebuilds the partition in the user's default tablespace, which may not be the correct location. Experienced DBAs double-size their index tablespaces so they can rebuild a partition in the same tablespace — the tablespace must have room for both the existing partition and the temporary segments for the new partition being built.

ALTER INDEX REBUILD PARTITION has four primary applications:

1. Reclustering: rebuild a partition to recover space and restore B-tree balance after heavy DML activity has fragmented the partition.
2. Media failure recovery: repair an index partition after a media failure on the volume where the partition resides or a software corruption of the segment containing it.
3. Post-load rebuild: regenerate a local index partition after loading the underlying table partition with Import or SQL*Loader. These utilities offer a performance option to bypass index maintenance during the load, marking affected index partitions UNUSABLE. The strategy of drop index then re-create index is replaced by the more efficient strategy of mark index partition UNUSABLE then rebuild index partition.
4. Post-partition-DDL rebuild: rebuild index partitions marked UNUSABLE by partition maintenance operations on the underlying table. Use UPDATE GLOBAL INDEXES on the ALTER TABLE statement to avoid this where possible.

For composite partitioned indexes, the SUBPARTITION syntax extends partition-level rebuild to the subpartition level:

ALTER INDEX index_name REBUILD SUBPARTITION subpartition_name
TABLESPACE tablespace_name;

SUBPARTITION can only be used with local partitioned indexes. Global partitioned indexes do not support SUBPARTITION rebuild. As with REBUILD PARTITION, always specify the TABLESPACE clause to ensure the rebuilt subpartition lands in the correct tablespace.

Improper sizing or growth can produce index fragmentation. Two remediation options are available: rebuild and coalesce. Before performing either, weigh the costs and benefits and choose the option that fits the situation.

Reorganizing selected partitions within an index rather than the entire index provides three specific advantages for table partitions:

1. A single partition can be reorganized faster than the whole table.
2. Individual partitions can be segregated into separate tablespaces independently of the other partitions.
3. Individual partitions can be made read-only independently of other table partitions — a valuable feature for data warehouses where historical partitions are no longer updated.

An index-organized table (IOT) stores and indexes data differently from an ordinary heap table. Understanding the distinction clarifies when partial index rebuilds apply and when they do not.

In an ordinary table, a row has a stable physical location identified by its ROWID. Once established, the ROWID does not change as long as the row exists. An index on an ordinary table stores both the column data and the ROWID, allowing Oracle to locate the row after finding the index entry.

In an index-organized table, rows do not have a stable physical location. Data is stored in sorted order in the leaf nodes of a B*-tree index built on the table's primary key. Rows can move to preserve sorted order — an insertion may cause an existing row to move to a different slot or block. The leaf nodes hold the primary key and the actual row data. All changes to table data — INSERT, UPDATE, DELETE — result in updates to the index only; there is no separate heap row storage.

IOTs are most useful when primary key access is the dominant pattern. They are less suitable for tables with many non-key columns accessed in patterns that do not filter on the primary key.

The partial index rebuild technique extends to composite partitioned indexes through the ALTER INDEX REBUILD SUBPARTITION syntax. SUBPARTITION can only be used with local partitioned indexes — global partitioned indexes do not support this feature. Always specify the TABLESPACE clause to ensure the rebuilt subpartition is placed in the correct tablespace.

Oracle recommends — but does not require — storing each index partition in a separate tablespace for three reasons:

1. Independent backup and recovery of each partition.
2. Reduced potential for data corruption spreading across partitions.
3. Control of partition-to-disk mapping for I/O load balancing.

Oracle introduced partitioning in Oracle 8 to provide a higher degree of manageability and availability. Individual partitions can be taken offline for maintenance while others remain available. In data warehousing, partitioning is commonly used to implement rolling windows based on date ranges. Oracle 23ai supports the following partitioning types:

• Range: divides data on a range of key values; most common for date-based data.
• Hash: distributes data evenly using a hash function; avoids I/O hotspots without a natural range key.
• List: partitions on discrete values such as region codes or status flags.
• Interval (Oracle 11g+): automatically creates new range partitions as data arrives; eliminates pre-creation of future partitions.
• Composite: combines two strategies — range-hash, range-list, list-range, list-hash, list-list, range-range — at two levels within the same table.

Hash partitioning distributes rows across partitions based on a hash function applied to the partition key. Data is randomly distributed rather than grouped by value. Use hash partitioning when:

1. Parallel partition-wise joins are needed with approximately equal-sized partitions.
2. Data must be distributed evenly across RAC nodes to minimize interconnect traffic for parallel DML operations.
3. Partition pruning and partition-wise joins are needed for queries constrained by distinct values or value lists rather than ranges.
4. Even distribution across storage devices is required to avoid I/O bottlenecks without a storage striping solution.

Note: hash partition pruning is limited to equality predicates. Range predicates on the partition key do not prune hash partitions.

Experienced DBAs double-size their index tablespaces so they can rebuild an index partition in the same tablespace. The tablespace must have sufficient room for both the existing index partition and the temporary segments required to build the new partition. The next lesson wraps up this module.