13.3.3 ALTER TABLE… ADD SUBPARTITION

Table of Contents Previous Next



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]
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 cannot use the ALTER TABLEADD SUBPARTITION statement to add a subpartition to a table with a MAXVALUE or DEFAULT rule , but you can split an existing subpartition with the ALTER TABLESPLIT SUBPARTITION statement, effectively adding a subpartition to a table.
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.
To use the ALTER TABLE... ADD SUBPARTITION command you must be the table owner, or have superuser (or administrative) privileges.
(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 Section 13.4.
The following example adds a RANGE subpartition to the list-partitioned sales table. The sales table was created with the command:
The sales table has three partitions, named europe, asia, and americas. Each partition has two range-defined subpartitions:
The following example adds a LIST subpartition to the RANGE partitioned sales table. The sales table was created with the command:
PARTITION BY RANGE(date)
SUBPARTITION BY LIST
(country)
(
PARTITION
first_half_2012 VALUES LESS THAN('01-JUL-2012')
(
SUBPARTITION europe VALUES ('ITALY', 'FRANCE'),
SUBPARTITION americas VALUES ('US', 'CANADA')
PARTITION second_half_2012 VALUES LESS THAN('01-JAN-2013')
(
SUBPARTITION asia VALUES ('INDIA', 'PAKISTAN')
After executing the above command, the sales table will have two partitions, named first_half_2012 and second_half_2012. The first_half_2012 partition has two subpartitions, named europe and americas, and the second_half_2012 partition has one partition, named asia:
The following command adds a subpartition to the second_half_2012 partition, named east_asia:


Table of Contents Previous Next