ALTER TABLE...ADD PARTITION v12

Use the ALTER TABLE… ADD PARTITION command to add a partition to an existing partitioned table. The syntax is:

ALTER TABLE <table_name> ADD PARTITION <partition_definition>;

Where partition_definition is:

        {<list_partition> | <range_partition>}

and list_partition is:

        PARTITION [<partition_name>]
          VALUES (<value>[, <value>]...)
          [TABLESPACE <tablespace_name>]
          [(<subpartition>, ...)]

and range_partition is:

        PARTITION [<partition_name>]
          VALUES LESS THAN (<value>[, <value>]...)
          [TABLESPACE <tablespace_name>]
          [(<subpartition>, ...)]

Where subpartition is:

        {<list_subpartition> | <range_subpartition> | <hash_subpartition>}

and list_subpartition is:

        SUBPARTITION [<subpartition_name>]
          VALUES (<value>[, <value>]...)
          [TABLESPACE <tablespace_name>]

and range_subpartition is:

        SUBPARTITION [<subpartition_name>]
          VALUES LESS THAN (<value>[, <value>]...)
          [TABLESPACE <tablespace_name>]

Description

The ALTER TABLE… ADD PARTITION command adds a partition to an existing partitioned table. There is no upper limit to the number of defined partitions in a partitioned table.

New partitions must be of the same type (LIST, RANGE, or HASH) as existing partitions. The new partition rules must reference the same column specified in the partitioning rules that define the existing partition(s).

You can use the ALTER TABLE… ADD PARTITION statement to add a partition to a table with a DEFAULT rule as long as there are no conflicting values between existing rows in the table and the values of the partition to be added.

You cannot use the ALTER TABLE… ADD PARTITION statement to add a partition to a table with a MAXVALUE rule.

You can alternatively use the ALTER TABLE… SPLIT PARTITION statement to split an existing partition, effectively increasing the number of partitions in a table.

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

Include the TABLESPACE clause to specify the tablespace in which the new partition will reside. If you do not specify a tablespace, the partition will reside in the default tablespace.

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

To use the ALTER TABLE... ADD PARTITION command you must be the table owner, or have superuser (or administrative) privileges.

Parameters

table_name

The name (optionally schema-qualified) of the partitioned table.

partition_name

The name of the partition to be created. Partition names must be unique amongst all partitions and subpartitions, and must follow the naming conventions for object identifiers.

subpartition_name

The name of the subpartition to be created. Subpartition names must be unique amongst all partitions and subpartitions, and must follow the naming conventions for object identifiers.

(value[, value]...)

Use value to specify a quoted literal value (or comma-delimited list of literal values) by which rows will be distributed into partitions. Each partitioning rule must specify at least one value, but there is no limit placed on the number of values specified within a rule. value may also be NULL, DEFAULT (if specifying a LIST partition), or MAXVALUE (if specifying a RANGE partition).

For information about creating a DEFAULT or MAXVALUE partition, see Handling Stray Values in a LIST or RANGE Partitioned Table.

tablespace_name

The name of the tablespace in which a partition or subpartition resides.