ALTER TABLE...ADD SUBPARTITION v12

The ALTER TABLE… ADD SUBPARTITION command adds a subpartition to an existing subpartitioned partition. The syntax is:

ALTER TABLE <table_name> MODIFY PARTITION <partition_name>
      ADD SUBPARTITION <subpartition_definition>;

Where subpartition_definition is:

{<list_subpartition> | <range_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 SUBPARTITION command adds a subpartition to an existing partition; the partition must already be subpartitioned. There is no upper limit to the number of defined subpartitions.

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

You can use the ALTER TABLE… ADD SUBPARTITION statement to add a subpartition 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 subpartition to be added.

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

You can split an existing subpartition with the ALTER TABLE… SPLIT SUBPARTITION statement, effectively adding a subpartition to a table.

You cannot add a new subpartition that precedes existing subpartitions in a range subpartitioned table; range subpartitions must be specified in ascending order.

Include the TABLESPACE clause to specify the tablespace in which the subpartition will reside. If you do not specify a tablespace, the subpartition will be created in the default tablespace.

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

To use the ALTER TABLE... ADD SUBPARTITION 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 in which the subpartition will reside.

partition_name

The name of the partition in which the new subpartition will reside.

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 table entries will be grouped 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 the subpartition resides.