Splitting a *-List Subpartition

Splitting a list subpartition of a *-list partitioned table is similar to the description in "Splitting a Partition of a List-Partitioned Table", but the syntax is that of SUBPARTITION rather than PARTITION. For example, the following statement splits a subpartition of the quarterly_regional_sales table:

ALTER TABLE quarterly_regional_sales SPLIT SUBPARTITION q2_1999_southwest
      ( SUBPARTITION q2_1999_utah
           TABLESPACE ts2,
        SUBPARTITION q2_1999_southwest
           TABLESPACE ts3

This operation splits the subpartition q2_1999_southwest into two subpartitions:

  • q2_1999_utah with literal value list of ('UT')

  • q2_1999_southwest which inherits the remaining literal value list of ('AZ','NM')

The individual subpartitions have new physical attributes that are inherited from the subpartition being split.

You can only split subpartitions in an interval-list partitioned table for range partitions or materialized interval partitions. To change subpartition values for future interval partitions, you must modify the subpartition template.