About Merging Partitions and Subpartitions

Use the ALTER TABLE MERGE PARTITION statement to merge the contents of two partitions into one partition. The two original partitions are dropped, as are any corresponding local indexes. You cannot use this statement for a hash partitioned table or for hash subpartitions of a composite *-hash partitioned table.

You cannot merge partitions for a reference-partitioned table. Instead, a merge operation on a parent table cascades to all descendant tables. However, you can use the DEPENDENT TABLES clause to set specific properties for dependent tables when you issue the merge operation on the master table to merge partitions or subpartitions.

See Also:

Oracle Database SQL Language Reference

If the involved partitions or subpartitions contain data, then indexes may be marked UNUSABLE as explained in the following table:

Table Type Index Behavior

Regular (Heap)

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

  • The database marks UNUSABLE all resulting corresponding local index partitions or subpartitions.

  • Global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.

Index-organized

  • The database marks UNUSABLE all resulting corresponding local index partitions.

  • All global indexes remain usable.

This section contains the following topics: