Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 12.1 CREATE TABLE…PARTITION BY

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

12.1 CREATE TABLE…PARTITION BY

Use the CREATE TABLE... PARTITION BY command to create a table with data distributed amongst one or more partitions. The syntax is:

CREATE TABLE [ schema. ]table_name
PARTITION BY {RANGE|LIST}(column[, column ]...)

[SUBPARTITION BY {RANGE | LIST} (column[, column ]...)]

(partition_definition[, partition_definition]...);

Where partition_definition is:

      {list_partition | range_partition }

and list_partition is:

      PARTITION partition_name

      VALUES (value[, value]...)

      [TABLESPACE tablespace_name]

      [(subpartition, ...)]

and range_partition is:

      PARTITION partition_name

      VALUES LESS THAN (value[, value]...)

      [TABLESPACE tablespace_name]

      [(subpartition, ...)]

Where subpartition 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 CREATE TABLE… PARTITION BY command creates a table with one or more partitions; each partition may have one or more subpartitions. There is no upper limit to the number of defined partitions, but if you include the PARTITION BY clause, you must specify at least one partitioning rule. The resulting table will be owned by the user that creates it.

Use the PARTITION BY LIST clause to divide a table into partitions based on the values entered in a specified column. Each partitioning rule must specify at least one literal value, but there is no upper limit placed on the number of values you may specify. Include a rule that specifies a matching value of DEFAULT to direct any un-qualified records to the given partition.

Use the PARTITION BY RANGE clause to specify boundary rules by which to create partitions. Each 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.

Range partition rules must be listed in ascending order to ensure that a given row ends up in the correct partition. INSERT commands that store records with values that exceed the top boundary of a RANGE partitioned table will fail. Include a boundary rule that specifies a value of MAXVALUE to direct any record that exceeds the values specified in the partitioning rules to a given partition; if you do not include a MAXVALUE rule, any record that exceeds the maximum limit specified by the boundary rules will result in an error.

If you use CREATE TABLE syntax to create an index on a partitioned table, the index will also be created in each partition or subpartition. If you add an index to a partitioned table using the CREATE INDEX command, indexes are not automatically created on partitions or subpartitions.

Parameters

table_name

The name (optionally schema-qualified) of the table to be created.

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. Each row will be stored in a partition that corresponds to the value of the specified column(s).

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

Example - PARTITION BY LIST

The following example creates a partitioned table (employees) using the PARTITION BY LIST clause. The employees table stores information in three partitions (mgmt, sales, and support):

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 sales VALUES ('SALESMAN', 'CLERK'),

PARTITION support VALUES ('ANALYST', 'DEFAULT'));

The partition that stores a specific entry is determined by the value entered in the job field. Administrative staff (with a job description of MANAGER or PRESIDENT) are stored in a partition named mgmt; employees with a job description of SALESMAN or CLERK are stored in a partition named sales.

Example - PARTITION BY RANGE

The following example creates a partitioned table named employees; the table stores employee information in three partitions (one_year_staff, five_year_staff and ten_year_staff) created using the PARTITION BY RANGE clause:

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'),

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

);

    ● The first partition, named ten_year_staff contains employee data for employees that have a hiredate before January 1, 2001.

    ● The second partition, named five_year_staff, contains employee data for those employees that have a hiredate before January 1, 2006.

    ● The third partition, named one_year_staff, contains employee data for those employees that have a hiredate before January 1, 2011.

Example - PARTITION BY RANGE, SUBPARTITION BY LIST

The following example combines the syntax of the previous examples to create a partitioned table that is first partitioned by RANGE; the RANGE partitions (one_year_staff, five_year_staff and ten_year_staff) are then subpartitioned by LIST subpartitioning rules. The LIST subpartition rules divide each RANGE partition into three partitions (mgmt, sales and support):

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 sr_mgmt VALUES ('MANAGER', 'PRESIDENT'),

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

SUBPARTITION sr_support VALUES ('ANALYST')

),

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

(

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

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

SUBPARTITION support VALUES ('ANALYST')

),

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

(

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

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

SUBPARTITION new_support VALUES ('ANALYST')

)

);

Previous PageTable Of ContentsNext Page