Add partitions and subpartitions using ADD PARTITION - PolarDB

Adds partitions and subpartitions to an existing partitioned table using ALTER TABLE...ADD PARTITION.

Syntax

ALTER TABLE table_name ADD PARTITION partition_definition;

partition_definition:

{list_partition | range_partition | hash_partition | key_partition}

list_partition:

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

range_partition:

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

hash_partition or key_partition:

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

subpartition:

{list_subpartition | range_subpartition | hash_partition | key_partition}

list_subpartition:

SUBPARTITION [subpartition_name]
VALUES IN (value[, value]...)
[TABLESPACE tablespace_name]

range_subpartition:

SUBPARTITION [subpartition_name]
VALUES LESS THAN (value[, value]...)
[TABLESPACE tablespace_name]

hash_partition or key_subpartition:

SUBPARTITION [subpartition_name]
[TABLESPACE tablespace_name]

Parameters

Parameter	Description
`table_name`	The name of the partitioned table. Can be schema-qualified.
`partition_name`	The name of the partition. Must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.
`subpartition_name`	The name of the subpartition. Must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.
`(value[, value]...)`	A quoted literal value, or a comma-separated list of literal values that determines how table entries are divided into partitions. Each partitioning rule must specify at least one value, with no upper limit on the number of values. Valid special values: `null`, `default` (LIST partitioning), and `maxvalue` (RANGE partitioning).
`tablespace_name`	The name of the tablespace where the partition or subpartition resides.

Usage notes

The target table must already be subpartitioned.
New partitions and subpartitions must match the types of existing ones.
The partitioning rule for new partitions must reference the same column used in the existing partitioning rule.
If no tablespace is specified (including for new subpartitions), the subpartition is created in the default tablespace.
If the partitioned table has indexes, indexes are created on the new subpartition.

Examples

Each example creates a partitioned table and then adds a new partition to it.

RANGE-RANGE partitioned table

In RANGE-partitioned tables, define partitions in ascending order of partition key values. New partitions cannot be added before an existing partition.

Create a table partitioned by RANGE and subpartitioned by RANGE.

CREATE TABLE sales_range_range
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE(dept_no)
SUBPARTITION BY RANGE(part_no)
(
  PARTITION p0 VALUES LESS THAN (1000) (
    SUBPARTITION s0 VALUES LESS THAN(100),
    SUBPARTITION s1 VALUES LESS THAN(200),
    SUBPARTITION s2 VALUES LESS THAN(300),
    SUBPARTITION s3 VALUES LESS THAN(MAXVALUE)
  )
);

Add a partition with four quarterly subpartitions.

ALTER TABLE sales_range_range ADD PARTITION (
  PARTITION p_2015 VALUES LESS THAN (2016)
  (
    SUBPARTITION q1_2015 VALUES LESS THAN(4),
    SUBPARTITION q2_2015 VALUES LESS THAN(7),
    SUBPARTITION q3_2015 VALUES LESS THAN(10),
    SUBPARTITION q4_2015 VALUES LESS THAN(13)
  )
);

LIST-RANGE partitioned table

Create a table partitioned by LIST and subpartitioned by RANGE.

CREATE TABLE sales_list_range
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY LIST (dept_no)
SUBPARTITION BY RANGE(amount)
(
  PARTITION p0 VALUES IN (1, 2)(
    SUBPARTITION s0 VALUES LESS THAN(1000),
    SUBPARTITION s1 VALUES LESS THAN(2000),
    SUBPARTITION s2 VALUES LESS THAN(3000),
    SUBPARTITION s3 VALUES LESS THAN(MAXVALUE)
  )
);

Add a partition for department numbers 7 and 8, with four amount-based subpartitions.

ALTER TABLE sales_list_range ADD PARTITION (
  PARTITION p3 VALUES IN (7, 8)(
    SUBPARTITION q1_2015 VALUES LESS THAN(4),
    SUBPARTITION q2_2015 VALUES LESS THAN(7),
    SUBPARTITION q3_2015 VALUES LESS THAN(10),
    SUBPARTITION q4_2015 VALUES LESS THAN(13)
  )
);

HASH-HASH partitioned table

The initial table has 9 HASH partitions on dept_no and 3 HASH subpartitions on part_no.

Create a table with HASH partitioning and HASH subpartitioning.

CREATE TABLE sales_hash_hash
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY HASH(dept_no) PARTITIONS 9
SUBPARTITION BY HASH(part_no) SUBPARTITIONS 3;

Add a partition with three subpartitions.

ALTER TABLE sales_hash_hash ADD PARTITION (
  PARTITION m3(
    SUBPARTITION d6,
    SUBPARTITION d7,
    SUBPARTITION d8
  )
);

KEY-KEY partitioned table

The initial table has 3 KEY partitions on dept_no and 2 KEY subpartitions on part_no.

Create a table with KEY partitioning and KEY subpartitioning.

CREATE TABLE sales_key_key
(
  dept_no     varchar(20),
  part_no     varchar(20),
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY KEY(dept_no) PARTITIONS 3
SUBPARTITION BY KEY(part_no) SUBPARTITIONS 2;

Add a partition with two subpartitions.

ALTER TABLE sales_key_key ADD PARTITION (
  PARTITION m3(
    SUBPARTITION d6,
    SUBPARTITION d7
  )
);