Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

12.3 ALTER TABLE… ADD SUBPARTITION

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.

The new subpartition rules must reference the column specified in the rules that define the existing subpartition(s). If the existing subpartitions are defined using LIST rules, any new subpartitions added with the ADD SUBPARTITION clause will also be defined using LIST rules. If the existing subpartitions use RANGE boundaries, any new subpartitions will also specify RANGE boundaries.

If the subpartitioned is indexed, the index will be replicated 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 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.

(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.

      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.

tablespace_name

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

Example - Adding a LIST subpartition to a RANGE partitioned table.

This example adds a LIST partition to the RANGE partitioned employees table. The employees table was created using the command:

CREATE TABLE employees

(

empno numeric(4,0),

ename varchar(10),

job varchar(9),

hiredate timestamp

)

PARTITION BY RANGE (hiredate)SUBPARTITION BY LIST (job)

(

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

(

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

SUBPARTITION sales VALUES ('SALESMAN', 'CLERK')

),

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

PARTITION one_year_staff VALUES LESS THAN('01-JAN-2011')

);

After executing the above command, the employees table will have three partitions - ten_year_staff, five_year_staff and one_year_staff. The ten_year_staff partition has two subpartitions; mgmt and sales.

The following command adds a third subpartition to the ten_year_staff partition, named support:

    ALTER TABLE employees MODIFY PARTITION ten_year_staff
    ADD SUBPARTITION support VALUES ('ANALYST');

Previous PageTable Of ContentsNext Page