About Coalescing Partitions and Subpartitions

Coalescing partitions is a way of reducing the number of partitions in a hash partitioned table or index, or the number of subpartitions in a *-hash partitioned table. When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by the database, and is dropped after its contents have been redistributed. If you coalesce a hash partition or subpartition in the parent table of a reference-partitioned table definition, then the reference-partitioned table automatically inherits the new partitioning definition.

Index partitions may be marked UNUSABLE as explained in the following table:

Table Type Index Behavior

Table Type	Index Behavior
Regular (Heap)	Unless you specify `UPDATE` `INDEXES` as part of the `ALTER` `TABLE` statement: Any local index partition corresponding to the selected partition is also dropped. Local index partitions corresponding to the one or more absorbing partitions are marked `UNUSABLE` and must be rebuilt. All global indexes, or all partitions of partitioned global indexes, are marked `UNUSABLE` and must be rebuilt.
Index-organized	Some local indexes are marked `UNUSABLE` as noted for heap indexes. All global indexes remain usable.

Regular (Heap)

Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:

Any local index partition corresponding to the selected partition is also dropped. Local index partitions corresponding to the one or more absorbing partitions are marked UNUSABLE and must be rebuilt.
All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.

Index-organized

Some local indexes are marked UNUSABLE as noted for heap indexes.
All global indexes remain usable.

This section contains the following topics: