Merging Subpartitions in a *-List Partitioned Table

You can merge the contents of any two arbitrary list subpartitions belonging to the same partition. The resulting subpartition value-list descriptor includes all of the literal values in the value lists for the partitions being merged.

The following statement merges two subpartitions of a table partitioned using range-list method into a new subpartition located in tablespace ts4:

ALTER TABLE quarterly_regional_sales
   MERGE SUBPARTITIONS q1_1999_northwest, q1_1999_southwest
      INTO SUBPARTITION q1_1999_west
         TABLESPACE ts4;

The value lists for the original two partitions were:

  • Subpartition q1_1999_northwest was described as ('WA','OR')

  • Subpartition q1_1999_southwest was described as ('AZ','NM','UT')

The resulting subpartition value list comprises the set that represents the union of these two subpartition value lists:

  • Subpartition q1_1999_west has a value list described as ('WA','OR','AZ','NM','UT')

The tablespace in which the resulting subpartition is located and the subpartition attributes are determined by the partition-level default attributes, except for those specified explicitly. If any of the existing subpartition names are being reused, then the new subpartition inherits the subpartition attributes of the subpartition whose name is being reused.