10.3.4 ALTER TABLE...SPLIT PARTITION

Table of Contents Previous Next



Use the ALTER TABLESPLIT PARTITION command to divide a single partition into two partitions, and redistribute the partition's contents between the new partitions. The command syntax comes in two forms.
The first form splits a RANGE partition into two partitions:
ALTER TABLE table_name SPLIT PARTITION partition_name
AT (range_part_value)
INTO
(
PARTITION
new_part1
[TABLESPACE tablespace_name],
PARTITION new_part2
[TABLESPACE tablespace_name]
);
The second form splits a LIST partition into two partitions:
ALTER TABLE table_name SPLIT PARTITION partition_name
VALUES (value[, value]...)
INTO
(
PARTITION
new_part1
[TABLESPACE tablespace_name],
PARTITION new_part2
[TABLESPACE tablespace_name]
);
The ALTER TABLE...SPLIT PARTITION command adds a partition to an existing LIST or RANGE partitioned table. Please note that the ALTER TABLESPLIT PARTITION command cannot add a partition to a HASH partitioned table. There is no upper limit to the number of partitions that a table may have.
When you execute an ALTER TABLE...SPLIT PARTITION command, Advanced Server creates two new partitions, and redistributes the content of the old partition between them (as constrained by the partitioning rules).
Include the TABLESPACE clause to specify the tablespace in which a partition will reside. If you do not specify a tablespace, the partition will reside in the default tablespace.
To use the ALTER TABLE... SPLIT PARTITION command you must be the table owner, or have superuser (or administrative) privileges.
new_part1 will receive the rows that meet the subpartitioning constraints specified in the ALTER TABLE… SPLIT SUBPARTITION command.
new_part2 will receive the rows are not directed to new_part1 by the partitioning constraints specified in the ALTER TABLE… SPLIT PARTITION command.
Use range_part_value to specify the boundary rules by which to create the new partition. The partitioning rule must contain at least one column of a data type that has two operators (i.e., a greater-than-or-equal to operator, and a less-than operator). Range boundaries are evaluated against a LESS THAN clause and are non-inclusive; a date boundary of January 1, 2010 will include only those date values that fall on or before December 31, 2009.
(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.
For information about creating a DEFAULT or MAXVALUE partition, see Section 10.4.
Our example will divide one of the partitions in the list-partitioned sales table into two new partitions, and redistribute the contents of the partition between them. The sales table is created with the statement:
The table definition creates three partitions (europe, asia, and americas). The following command adds rows to each partition:
The following command splits the americas partition into two partitions named us and canada:
A SELECT statement confirms that the rows have been redistributed across the correct partitions:
This example divides the q4_2012 partition (of the range-partitioned sales table) into two partitions, and redistribute the partition's contents. Use the following command to create the sales table:
The table definition creates four partitions (q1_2012, q2_2012, q3_2012, and q4_2012). The following command adds rows to each partition:
A SELECT statement confirms that the rows are distributed amongst the partitions as expected:
The following command splits the q4_2012 partition into two partitions named q4_2012_p1 and q4_2012_p2:
A SELECT statement confirms that the rows have been redistributed across the new partitions:


Table of Contents Previous Next