Add partitions and subpartitions to an existing partitioned table - PolarDB

This topic describes how to add partitions and subpartitions to an existing partitioned table.

Syntax

You can use the ALTER TABLE…ADD PARTITION statement to add partitions and subpartitions to an existing partitioned table.

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, which can be schema-qualified.
partition_name	The name of the partition. Partition names must be unique among all partitions and subpartitions and must follow the naming conventions for object identifiers.
subpartition_name	The name of the subpartition. Subpartition names must be unique among all partitions and subpartitions and must follow the naming conventions for object identifiers.
(value[, value]...)	`value` specifies a quoted literal value, or a comma-separated list of literal values by which table entries are divided into different partitions. Each partitioning rule must specify at least one value. The number of values specified in a rule is not limited. The `value` can be `null`, `default` (`list` partitioning), or `maxvalue` (`range` partitioning).
tablespace_name	The name of the tablespace in which the partition or subpartition resides.

Usage notes

The ALTER TABLE…ADD PARTITION statement is used to add partitions and subpartitions to an existing partitioned table. Make sure that the partitioned table is subpartitioned. The new partitions and subpartitions must be of the same types as the existing partitions and subpartitions in the partitioned table. The partitioning rule for new partitions must reference the same column specified in the partitioning rule that defines the existing partitions.

If you do not specify a tablespace (including the tablespace to which the new subpartition belongs), the subpartition is created in the default tablespace.

If the partitioned table is indexed, the index is created on the new subpartition.

Examples

Note

When you define range partitions, order the partitions in ascending order of the partition key values. New partitions cannot be added before an existing partition in a RANGE partitioned table.

Create a partitioned table named sales_range_range and add partitions and subpartitions to the table.

Create a table named sales_range_range that is partitioned by RANGE and further 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)
  )
);

Use the ALTER TABLE…ADD PARTITION statement to add partitions and subpartitions to the sales_range_range partitioned table.

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

Create a partitioned table named sales_list_range and add partitions and subpartitions to the table.

Create a partitioned table named sales_list_range that is partitioned by LIST and further 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)
  )
);

Use the ALTER TABLE…ADD PARTITION statement to add partitions and subpartitions to the sales_list_range partitioned table.

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

Create a partitioned table named sales_hash_hash and add partitions and subpartitions to the table.

Create a partitioned table named sales_hash_hash that is partitioned by HASH and further partitioned by HASH.

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
;

Use the ALTER TABLE…ADD PARTITION statement to add partitions and subpartitions to the sales_hash_hash partitioned table.

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

Create a partitioned table named sales_key_key and add partitions and subpartitions to the table.

Create a partitioned table named sales_key_key that is partitioned by KEY and further partitioned by KEY.

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;

Use the ALTER TABLE…ADD PARTITION statement to add partitions and subpartitions to the sales_key_key partitioned table.
```
ALTER TABLE sales_key_key ADD PARTITION (
   PARTITION m3(
    SUBPARTITION d6,
    SUBPARTITION d7
  )
);
```