This topic describes how to add partitions and subpartitions to an existing partitioned table.

Syntax

The ALTER TABLE... SPLIT PARTITION statement is used to add partitions and subpartitions to an existing partitioned table. 

ALTER TABLE table_name ADD PARTITION partition_definition;
partition_definition is:
{list_partition | range_partition | hash_partition | key_partition}
list_partition is:
PARTITION [partition_name]
VALUES IN (value[, value]...)
[TABLESPACE tablespace_name] 
(subpartition, ...)
range_partition is:
PARTITION partition_name
VALUES LESS THAN (value[, value]...)
[TABLESPACE tablespace_name]
[(subpartition, ...)]
hash_partition and key_partition are:
PARTITION partition_name
[TABLESPACE tablespace_name]
(subpartition, ...)
subpartition is:
{list_subpartition | range_subpartition | hash_partition | key_partition}
list_subpartition is:
SUBPARTITION [subpartition_name]
VALUES IN (value[, value]...)
[TABLESPACE tablespace_name]
range_subpartition is:
SUBPARTITION [subpartition_name ]
VALUES LESS THAN (value[, value]...)
[TABLESPACE tablespace_name]
hash_partition and key_partition are:
SUBPARTITION [subpartition_name ]
[TABLESPACE tablespace_name]

Parameters

Parameter Description
table_name The name (optionally schema-qualified) of the partitioned table.
partition_name The name of the partition. Partition names must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.
subpartition_name The name of the subpartition. Subpartition names must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.
(value[, value]...) value specifies a quoted literal value (or a list of literal values separated by commas) by which table entries will be grouped into partitions. Each partitioning rule must specify at least one value, but the number of values specified in a rule is not limited. value may be null, default (if specifying a list partition), or maxvalue (if specifying a range partition).
tablespace_name The name of the tablespace in which the partition or subpartition resides.

Precautions

The ALTER TABLE…ADD PARTITION statement adds partitions and subpartitions to an existing partition. The existing partition must have been subpartitioined. The new partitions and subpartitions must be of the same types as the existing partition and subpartition. The partitioning rules for new partitions must reference the same column specified in the partitioning rules that define the existing partitions.

Range partitions must be specified in ascending order. You cannot add a new partition that precedes existing partitions in a range partitioned table.

If you do not specify a tablespace (including the tablespace to which the new subpartition belongs), the subpartition is created in the default tablespace.

If the table is indexed, the index will be created on the new subpartition.

Examples

Add partitions to the sales_range_range partitioned table:
ALTER TABLE sales_range_range ADD PARTITION (
    PARTITION p_2015 VALUES less than (2016)
    (
    SUBPARTITION q1_2015 VALUES LESS THAN(4),
    SUBPARTITION q2_2015 VALUES LESS THAN(7),
    SUBPARTITION q3_2015 VALUES LESS THAN(10),
    SUBPARTITION q4_2015 VALUES LESS THAN(13)
  )
);
Add partitions to the sales_list_range partitioned table:
ALTER TABLE sales_list_range ADD PARTITION (
   PARTITION p3 VALUES in (7, 8)(
    SUBPARTITION q1_2015 VALUES LESS THAN(4),
    SUBPARTITION q2_2015 VALUES LESS THAN(7),
    SUBPARTITION q3_2015 VALUES LESS THAN(10),
    SUBPARTITION q4_2015 VALUES LESS THAN(13)
  )
);
Add partitions to the sales_hash_hash partitioned table:
ALTER TABLE sales_hash_hash ADD PARTITION (
   PARTITION m3(
    SUBPARTITION d6,
    SUBPARTITION d7
  )
);
Add partitions to the sales_key_key partitioned table:
ALTER TABLE sales_key_key ADD PARTITION (
   PARTITION m3(
    SUBPARTITION d6,
    SUBPARTITION d7
  )
);