Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

12.7 ALTER TABLE...SPLIT SUBPARTITION

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

      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]
      );

The second variation splits a LIST partition into two subpartitions:

      ALTER TABLE table_name SPLIT SUBPARTITION subpartition_name
      VALUES (value[, value]...)
      INTO
      (
      PARTITION new_subpart1 [TABLESPACE tablespace_name],
      PARTITION new_subpart2 [TABLESPACE tablespace_name]
      );

Description

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 the specified values (in the case of a LIST subpartition) or which are less than the specified values (in the case of a RANGE subpartition) into new_subpart1, and any remaining rows into new_subpart2.

The new subpartition rules will 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 SPLIT SUBPARTITION clause must also be defined using LIST rules. If the existing subpartitions use RANGE boundaries, any new subpartitions must also specify RANGE boundaries.

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

To use the ALTER TABLE... SPLIT 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.

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

This example will split the europe_2000 subpartition in the sales_hist table (created with the following statement) into two subpartitions:

      CREATE TABLE salesHist
      (sales_year int, country varchar2(30), amount numeric)

      PARTITION BY RANGE(sales_year) SUBPARTITION BY LIST(country)

      (

      PARTITION sales_2000 VALUES LESS THAN(2001)

      (

      SUBPARTITION fareast_2000 VALUES ('JAPAN', 'VIETNAM'),

      SUBPARTITION europe_2000 VALUES ('GERMANY', 'ITALY'),

      SUBPARTITION west_2000 VALUES ('AMERICA', 'CANADA'),

      SUBPARTITION east_2000 VALUES ('INDIA', 'PAKISTAN'),

      SUBPARTITION rest_2000 VALUES (DEFAULT)

      ),

      PARTITION sales_2001 VALUES LESS THAN(2002)

      (

      SUBPARTITION fareast_2001 VALUES ('JAPAN', 'VIETNAM'),

      SUBPARTITION europe_2001 VALUES ('GERMANY', 'ITALY'),

      SUBPARTITION west_2001 VALUES ('AMERICA', 'CANADA'),

      SUBPARTITION east_2001 VALUES ('INDIA', 'PAKISTAN'),

      SUBPARTITION rest_2001 VALUES (DEFAULT)

      )

      );

The following statement splits the europe_2000 subpartition into two subpartitions (europe_south_2000 and europe_north_2000):

      ALTER TABLE salesHist
      SPLIT SUBPARTITION europe_2000 VALUES('ITALY') INTO
      (
      SUBPARTITION europe_south_2000,

      SUBPARTITION europe_north_2000

      );

Previous PageTable Of ContentsNext Page