Merging Multiple Partitions

You can merge the contents of two or more partitions or subpartitions into one new partition or subpartition and then drop the original partitions or subpartitions with the MERGE PARTITIONS and MERGE SUBPARTITIONS clauses of the ALTER TABLE SQL statement. The MERGE PARTITIONS and MERGE SUBPARTITIONS clauses are synonymous with the MERGE PARTITION and MERGE SUBPARTITION clauses.

For example, the following SQL statement merges four partitions into one partition and drops the four partitions that were merged.

ALTER TABLE t1 MERGE PARTITIONS p01, p02, p03, p04 INTO p0;

When merging multiple range partitions, the partitions must be adjacent and specified in the ascending order of their partition bound values. The new partition inherits the partition upper bound of the highest of the original partitions.

You can specify the lowest and the highest partitions to be merged when merging multiple range partitions with the TO syntax. All partitions between specified partitions, including those specified, are merged into the target partition. You cannot use this syntax for list and system partitions.

For example, the following SQL statements merges partitions p01 through p04 into the partition p0.

ALTER TABLE t1 MERGE PARTITIONS p01 TO p04 INTO p0;

List partitions and system partitions that you want to merge do not need to be adjacent, because no ordering of the partitions is assumed. When merging multiple list partitions, the resulting partition value list are the union of the set of partition value list of all of the partitions to be merged. A DEFAULT list partition merged with other list partitions results in a DEFAULT partition.

When merging multiple partitions of a composite partitioned table, the resulting new partition inherits the subpartition descriptions from the subpartition template, if one exists. If no subpartition template exists, then Oracle creates one MAXVALUE subpartition from range subpartitions or one DEFAULT subpartition from list subpartitions for the new partition. When merging multiple subpartitions of a composite partitioned table, the subpartitions to be merged must belong to the same partition.

When merging multiple partitions, local and global index operations and semantics for inheritance of unspecified physical attributes are the same for merging two partitions.

In the following SQL statement, four partitions of the partitioned by range table sales are merged. These four partitions that correspond to the four quarters of the oldest year are merged into a single partition containing the entire sales data of the year.

ALTER TABLE sales
  MERGE PARTITIONS sales_q1_2009, sales_q2_2009, sales_q3_2009, sales_q4_2009
  INTO PARTITION sales_2009;

The previous SQL statement can be rewritten as the following SQL statement to obtain the same result.

ALTER TABLE sales
  MERGE PARTITIONS sales_q1_2009 TO sales_q4_2009
  INTO PARTITION sales_2009;