Merging Partitions in a *-Range Partitioned Table

Merging partitions in a *-range partitioned table is as described previously in "Merging Range Partitions". However, when you merge two *-range partitions, the resulting new partition inherits the subpartition descriptions from the subpartition template, if one exists. If no subpartition template exists, then a single subpartition with an upper boundary MAXVALUE is created for the new partition.

For interval-range partitioned tables, you can only merge two adjacent interval partitions, or the highest range partition with the first interval partition. As described in "Merging Interval Partitions", the transition point moves when you merge intervals in an interval-range partitioned table.

The following statement merges two partitions in the monthly interval-range partitioned orders table. A subpartition template exists for the table.

MERGE PARTITIONS FOR(TO_DATE('01-MAR-2007','dd-MON-yyyy')), 
INTO PARTITION p_pre_may_2007;

If the March 2007 and April 2007 partitions were still in the interval section of the interval-range partitioned table, then the merge operation would move the transition point to May 1, 2007.

The subpartitions for partition p_pre_may_2007 inherit their properties from the subpartition template. The data in the resulting partitions consists of data from both the partitions. However, there may be cases where the database returns an error. This can occur because data may map out of the new partition when both of the following conditions are met:

The error condition can be eliminated by always specifying a subpartition with an upper boundary of MAXVALUE in the subpartition template.

  • Some range values of the merged subpartitions were not included in the subpartition template.

  • The subpartition template does not have a subpartition definition with a MAXVALUE upper boundary.