10.3.5 ALTER TABLE...SPLIT SUBPARTITION

Table of Contents Previous Next



Use the ALTER TABLESPLIT SUBPARTITION command to divide a single subpartition into two subpartitions, and redistribute the subpartition's contents. The command comes in two variations.
ALTER TABLE table_name SPLIT SUBPARTITION subpartition_name
AT (
range_part_value)
INTO
(
SUBPARTITION
new_subpart1
[TABLESPACE
tablespace_name],
SUBPARTITION
new_subpart2
[TABLESPACE
tablespace_name]
);
ALTER TABLE table_name SPLIT SUBPARTITION subpartition_name
VALUES (value[, value]...)
INTO
(
SUBPARTITION
new_subpart1
[TABLESPACE
tablespace_name],
SUBPARTITION
new_subpart2
[TABLESPACE
tablespace_name]
);
The ALTER TABLE...SPLIT SUBPARTITION command adds a subpartition to an existing subpartitioned table. There is no upper limit to the number of defined subpartitions. When you execute an ALTER TABLE...SPLIT SUBPARTITION command, Advanced Server creates two new subpartitions, moving any rows that contain values that are constrained by the specified subpartition rules into new_subpart1, and any remaining rows into new_subpart2.
Include the TABLESPACE clause to specify a tablespace in which a new subpartition will reside. If you do not specify a tablespace, the subpartition will be created in the default tablespace.
To use the ALTER TABLE... SPLIT SUBPARTITION command you must be the table owner, or have superuser (or administrative) privileges.
new_subpart1 will receive the rows that meet the subpartitioning constraints specified in the ALTER TABLE… SPLIT SUBPARTITION command.
new_subpart2 will receive the rows are not directed to new_subpart1 by the subpartitioning constraints specified in the ALTER TABLE… SPLIT SUBPARTITION command.
(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 subpartition), or MAXVALUE (if specifying a RANGE subpartition).
For information about creating a DEFAULT or MAXVALUE partition, see Section 10.4.
PARTITION BY RANGE(date)
SUBPARTITION BY LIST
(country)
(
PARTITION
first_half_2012 VALUES LESS THAN('01-JUL-2012')
(
SUBPARTITION p1_europe VALUES ('ITALY', 'FRANCE'),
SUBPARTITION p1_americas VALUES ('US', 'CANADA')
PARTITION second_half_2012 VALUES LESS THAN('01-JAN-2013')
(
SUBPARTITION p2_europe VALUES ('ITALY', 'FRANCE'),
SUBPARTITION p2_americas VALUES ('US', 'CANADA')
The sales table has two partitions, named first_half_2012, and second_half_2012. Each partition has two range-defined subpartitions that distribute the partition's contents into subpartitions based on the value of the country column:
A SELECT statement confirms that the rows are correctly distributed amongst the subpartitions:
The following command splits the p2_americas subpartition into two new subpartitions, and redistributes the contents:
After invoking the command, the p2_americas subpartition has been deleted; in its place, the server has created two new subpartitions (p2_us and p2_canada):
Querying the sales table demonstrates that the content of the p2_americas subpartition has been redistributed:
The sales table has three partitions (europe, asia, and americas). Each partition has two range-defined subpartitions that sort the partitions contents into subpartitions by the value of the date column:
A SELECT statement confirms that the rows are distributed amongst the subpartitions:
The following command splits the americas_2012 subpartition into two new subpartitions, and redistributes the contents:
After invoking the command, the americas_2012 subpartition has been deleted; in its place, the server has created two new subpartitions (americas_p1_2012 and americas_p2_2012):
Querying the sales table demonstrates that the subpartition's contents are redistributed:


Table of Contents Previous Next