You split a list partition by using the
PARTITION statement. The
PARTITION clause enables you to specify a list of literal values that define a partition into which rows with corresponding partitioning key values are inserted. The remaining rows of the original partition are inserted into a second partition whose value list contains the remaining values from the original partition. You can optionally specify new attributes for the two partitions that result from the split.
The following statement splits the partition
region_east into two partitions:
ALTER TABLE sales_by_region SPLIT PARTITION region_east VALUES ('CT', 'MA', 'MD') INTO ( PARTITION region_east_1 TABLESPACE tbs2, PARTITION region_east_2 STORAGE (INITIAL 8M)) PARALLEL 5;
The literal value list for the original
region_east partition was specified as:
PARTITION region_east VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')
The two new partitions are:
region_east_1 with a literal value list of
region_east_2 inheriting the remaining literal value list of
The individual partitions have new physical attributes specified at the partition level. The operation is executed with parallelism of degree 5.
You can split a default list partition just like you split any other list partition. This is also the only means of adding a partition to a list-partitioned table that contains a default partition. When you split the default partition, you create a new partition defined by the values that you specify, and a second partition that remains the default partition.
The following example splits the default partition of
sales_by_region, thereby creating a new partition:
ALTER TABLE sales_by_region SPLIT PARTITION region_unknown VALUES ('MT', 'WY', 'ID') INTO ( PARTITION region_wildwest, PARTITION region_unknown);