Use CREATE TABLE... PARTITION BY to create list and range partitioned tables - PolarDB

Use the PARTITION BY clause of the CREATE TABLE statement to create a partitioned table. Data is distributed among one or more partitions and, optionally, subpartitions.

Syntax

List partitioning

CREATE TABLE [ schema. ]table_name
  table_definition
  PARTITION BY LIST(column)
  [SUBPARTITION BY {RANGE|LIST|HASH} (column[, column ]...)]
  (list_partition_definition[, list_partition_definition]...);

Where list_partition_definition is:

PARTITION [partition_name]
  VALUES (value[, value]...)
  [TABLESPACE tablespace_name]
  [(subpartition, ...)]

Range partitioning

CREATE TABLE [ schema. ]table_name
  table_definition
  PARTITION BY RANGE(column[, column ]...)
  [SUBPARTITION BY {RANGE|LIST|HASH} (column[, column ]...)]
  (range_partition_definition[, range_partition_definition]...);

Where range_partition_definition is:

PARTITION [partition_name]
  VALUES LESS THAN (value[, value]...)
  [TABLESPACE tablespace_name]
  [(subpartition, ...)]

Subpartitioning

A subpartition is one of:

{list_subpartition | range_subpartition}

Where 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

CREATE TABLE... PARTITION BY creates a table with one or more partitions, each of which may contain one or more subpartitions. The number of partitions is not limited. Include at least one partitioning rule when using the PARTITION BY clause. The resulting table is owned by the user who creates it.

PARTITION BY LIST

Use PARTITION BY LIST to route rows into partitions based on the value of a specified column. Each partitioning rule must specify at least one literal value; there is no upper limit on the number of values per rule.

Include a DEFAULT rule as the last partitioning rule to catch any rows that do not match another partition's values. Without a DEFAULT partition, any INSERT that does not match an existing rule fails with an error.

PARTITION BY RANGE

Use PARTITION BY RANGE to route rows into partitions based on column value ranges. Each partitioning rule requires at least one column with a data type that supports comparison operators.

Range boundaries are evaluated using LESS THAN and are non-inclusive: a boundary of 2013-Jan-01 captures only rows with values on or before December 31, 2012.

Define partitioning rules in ascending order. If a row's partition key exceeds the highest defined boundary, the INSERT fails unless a MAXVALUE rule is present. Include MAXVALUE as the last rule to prevent this error.

SUBPARTITION BY

When SUBPARTITION BY is included in the table definition, each partition contains at least one subpartition. Subpartitions can be explicitly defined or system-generated.

System-generated subpartitions are created in the default tablespace, and the server assigns their names as a combination of the partition name and a unique identifier:

When SUBPARTITION BY LIST is specified, the server creates a DEFAULT subpartition.
When SUBPARTITION BY RANGE is specified, the server creates a MAXVALUE subpartition.

To view all subpartition names, query ALL_TAB_SUBPARTITIONS.

TABLESPACE

Use the TABLESPACE keyword to specify the tablespace for a partition or subpartition. If omitted, the partition or subpartition is created in the default tablespace.

Indexes on partitioned tables

When you create an index on a partitioned table using CREATE TABLE syntax, the index is created on each partition and subpartition.

Parameters

Parameter	Description
`table_name`	The name of the table to be created, optionally schema-qualified.
`table_definition`	Column names, data types, and constraint information, as described in the PostgreSQL core documentation for `CREATE TABLE`.
`partition_name`	The name of the partition. Must be unique across all partitions and subpartitions, and must follow naming conventions for object identifiers.
`subpartition_name`	The name of the subpartition. Must be unique across all partitions and subpartitions, and must follow naming conventions for object identifiers.
`column`	The column on which partitioning rules are based. Each row is stored in the partition that matches the column's value.
`(value[, value]...)`	One or more quoted literal values that define partition membership. The value may be `NULL`, `DEFAULT` (for `LIST` partitions), or `MAXVALUE` (for `RANGE` partitions).
`tablespace_name`	The tablespace in which the partition or subpartition resides.

Examples

PARTITION BY LIST

The following example creates a list-partitioned sales table with three partitions based on the country column.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
(
  PARTITION europe   VALUES('FRANCE', 'ITALY'),
  PARTITION asia     VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);

Query ALL_TAB_PARTITIONS to verify the partition layout:

SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;

 partition_name |     high_value
----------------+---------------------
 americas       | 'US', 'CANADA'
 asia           | 'INDIA', 'PAKISTAN'
 europe         | 'FRANCE', 'ITALY'
(3 rows)

Row routing:

Rows with country = 'US' or 'CANADA' go to the americas partition.
Rows with country = 'INDIA' or 'PAKISTAN' go to the asia partition.
Rows with country = 'FRANCE' or 'ITALY' go to the europe partition.

The following INSERT matches the europe partitioning rule and is stored in the europe partition:

INSERT INTO sales VALUES (10, '9519a', 'FRANCE', '18-Aug-2012', '650000');

PARTITION BY RANGE

The following example creates a range-partitioned sales table with four quarterly partitions based on the date column.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date)
(
  PARTITION q1_2012 VALUES LESS THAN('2012-Apr-01'),
  PARTITION q2_2012 VALUES LESS THAN('2012-Jul-01'),
  PARTITION q3_2012 VALUES LESS THAN('2012-Oct-01'),
  PARTITION q4_2012 VALUES LESS THAN('2013-Jan-01')
);

Query ALL_TAB_PARTITIONS to verify the partition layout:

SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;

 partition_name |                            high_value
----------------+------------------------------------------------------------------
 q1_2012        | FOR VALUES FROM (MINVALUE) TO ('01-APR-12 00:00:00')
 q2_2012        | FOR VALUES FROM ('01-APR-12 00:00:00') TO ('01-JUL-12 00:00:00')
 q3_2012        | FOR VALUES FROM ('01-JUL-12 00:00:00') TO ('01-OCT-12 00:00:00')
 q4_2012        | FOR VALUES FROM ('01-OCT-12 00:00:00') TO ('01-JAN-13 00:00:00')
(4 rows)

Row routing:

Rows with date before April 1, 2012 go to q1_2012.
Rows with date before July 1, 2012 go to q2_2012.
Rows with date before October 1, 2012 go to q3_2012.
Rows with date before January 1, 2013 go to q4_2012.

The following INSERT matches the q3_2012 boundary and is stored in the q3_2012 partition:

INSERT INTO sales VALUES (10, '9519a', 'FRANCE', '18-Aug-2012', '650000');

PARTITION BY RANGE, SUBPARTITION BY LIST

The following example creates a sales table partitioned first by transaction date (range), then subpartitioned by country (list). The result is four range partitions, each with three list subpartitions — 12 subpartitions in total.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date)
  SUBPARTITION BY LIST(country)
  (
    PARTITION q1_2012 VALUES LESS THAN('2012-Apr-01')
      (
        SUBPARTITION q1_europe   VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q1_asia     VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q1_americas VALUES ('US', 'CANADA')
      ),
    PARTITION q2_2012 VALUES LESS THAN('2012-Jul-01')
      (
        SUBPARTITION q2_europe   VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q2_asia     VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q2_americas VALUES ('US', 'CANADA')
      ),
    PARTITION q3_2012 VALUES LESS THAN('2012-Oct-01')
      (
        SUBPARTITION q3_europe   VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q3_asia     VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q3_americas VALUES ('US', 'CANADA')
      ),
    PARTITION q4_2012 VALUES LESS THAN('2013-Jan-01')
      (
        SUBPARTITION q4_europe   VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q4_asia     VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q4_americas VALUES ('US', 'CANADA')
      )
  );

Query ALL_TAB_SUBPARTITIONS to verify the subpartition layout:

SELECT subpartition_name, high_value, partition_name FROM ALL_TAB_SUBPARTITIONS;

 subpartition_name |             high_value              | partition_name
-------------------+-------------------------------------+----------------
 q1_americas       | FOR VALUES IN ('US', 'CANADA')      | q1_2012
 q1_asia           | FOR VALUES IN ('INDIA', 'PAKISTAN') | q1_2012
 q1_europe         | FOR VALUES IN ('FRANCE', 'ITALY')   | q1_2012
 q2_americas       | FOR VALUES IN ('US', 'CANADA')      | q2_2012
 q2_asia           | FOR VALUES IN ('INDIA', 'PAKISTAN') | q2_2012
 q2_europe         | FOR VALUES IN ('FRANCE', 'ITALY')   | q2_2012
 q3_americas       | FOR VALUES IN ('US', 'CANADA')      | q3_2012
 q3_asia           | FOR VALUES IN ('INDIA', 'PAKISTAN') | q3_2012
 q3_europe         | FOR VALUES IN ('FRANCE', 'ITALY')   | q3_2012
 q4_americas       | FOR VALUES IN ('US', 'CANADA')      | q4_2012
 q4_asia           | FOR VALUES IN ('INDIA', 'PAKISTAN') | q4_2012
 q4_europe         | FOR VALUES IN ('FRANCE', 'ITALY')   | q4_2012
(12 rows)

Row routing: when a row is inserted, the server first evaluates the date value against the range partitioning rules to select a partition, then evaluates the country value against the list subpartitioning rules to select a subpartition. Every row is stored in a subpartition, so all top-level partitions hold no data directly.

The following INSERT is stored in the q3_europe subpartition (date falls in Q3 2012, country is 'FRANCE'):

INSERT INTO sales VALUES (10, '9519a', 'FRANCE', '18-Aug-2012', '650000');