Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

12.6 ALTER TABLE...SPLIT PARTITION

Use the ALTER TABLESPLIT PARTITION command to divide a single partition into two partitions. The command comes in two variations; the first variation splits a RANGE partition into two partitions:

      ALTER TABLE table_name SPLIT PARTITION partition_name
      AT (range_part_value)
      INTO
      (
      PARTITION new_part1 [TABLESPACE tablespacename],
      PARTITION new_part2 [TABLESPACE tablespacename]
      );

The second variation splits a LIST partition into two partitions:

      ALTER TABLE table_name SPLIT PARTITION partition_name
      VALUES (value[, value]...)
      INTO
      (
      PARTITION new_part1 [TABLESPACE tablespacename],
      PARTITION new_part2 [TABLESPACE tablespacename]
      );

Description

The ALTER TABLE...SPLIT PARTITION command adds a partition to an existing partitioned table. There is no upper limit to the number of defined partitions. When you execute an ALTER TABLE...SPLIT PARTITION command, Advanced Server creates two new partitions, moving any rows that contain the specified values (in the case of a LIST partition) or which are less than the specified values (in the case of a RANGE partition) into new_part1, and any remaining rows into new_part2.

If the existing partitions are defined using LIST rules, any new partitions added with the SPLIT PARTITION clause must also be defined using LIST rules. If the existing partitions use RANGE boundaries, any new partitions must also specify RANGE boundaries.

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

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

Parameters

table_name

The name (optionally schema-qualified) of the modified 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.

column

The name of a column on which the partitioning rules are based. The value stored in the specified column will determine which partition that the record will be stored in.

range_part_value

      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.

      When specifying rules for a RANGE partitioned table, include the MAXVALUE keyword in the last partition rule to direct any un-categorized records to the given partition. If you do not include a rule that includes a value of MAXVALUE, any INSERT statement that attempts to add a record with a value greater than the highest value specified will fail, and return an error.

(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 be NULL, DEFAULT (if specifying a LIST partition), or MAXVALUE (if specifying a RANGE partition).

      When specifying rules for a LIST partitioned table, include the DEFAULT keyword in the last partition rule to direct any un-matched records to the given partition. If you do not include a rule that includes a value of DEFAULT, any INSERT statement that attempts to add a record that does not match the specified rules of at least one partition will fail, and return an error.

tablespace_name

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

Example - Splitting a LIST partition.

Our example will divide one of the partitions in the employees table (created with the following statement) into two partitions:

CREATE TABLE employees

(
empno numeric(4,0),

ename varchar(10),

job varchar(9),

hiredate timestamp

)

PARTITION BY LIST (job)

(
PARTITION mgmt VALUES ('MANAGER', 'PRESIDENT'),

PARTITION staff VALUES ('SALESMAN', 'CLERK', 'ANALYST')
);

The table definition creates two partitions; one partition contains management employees, and the other, non-management employees. The following command uses the VALUES keyword to divide the staff partition into two LIST partitions, sales and support:

ALTER TABLE employees SPLIT PARTITION staff
VALUES ('SALESMAN', 'CLERK')
INTO (PARTITION sales, PARTITION support);

Example - Splitting a RANGE partition.

Our example will divide one of the partitions in the employees table (created with the following statement) into two partitions:

CREATE TABLE employees

(
empno numeric(4,0),

ename varchar(10),

job varchar(9),

hiredate timestamp

)

PARTITION BY RANGE (hiredate)

(
PARTITION ten_year_staff VALUES LESS THAN('01-JAN-2001'),

PARTITION five_year_staff VALUES LESS THAN('01-JAN-2006')
);

Use the following command to split the five_year_staff partition into two partitions, one_year_staff and five_year_staff:

ALTER TABLE employees SPLIT PARTITION five_year_staff
AT ('01-JAN-2010')
INTO (PARTITION one_year_staff, PARTITION five_year_staff);

Previous PageTable Of ContentsNext Page