Use ALTER TABLE to modify table partitions (AUTO mode) - PolarDB

This topic describes how to use the ALTER TABLE statement to modify partitions in a table. You can execute the ALTER TABLE statement to split, merge, and migrate partitions. This syntax applies to only AUTO mode databases.

Split a partition into multiple partitions

You can split a partition in a table into multiple partitions.

Use the RANGE or RANGE COLUMN partitioning method to split a partition

Syntax

ALTER TABLE identifier SPLIT PARTITION identifier INTO
 (partition_definition,partition_definition, [, partition_definition] ...);

 partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(value_list) | MAXVALUE}]

Example

Execute the following SQL statement to create a table named tb1:

CREATE TABLE tb1(a int) PARTITION BY RANGE(a)
 (PARTITION p1 VALUES LESS THAN(20),
  PARTITION p2 VALUES LESS THAN(100))

Execute the following SQL statement to split the p1 partition into three partitions named p10, p11, and p12:

ALTER TABLE tb1 SPLIT PARTITION p1 INTO
(PARTITION p10 VALUES LESS THAN (8),
PARTITION p11 VALUES LESS THAN(15),
PARTITION p12 VALUES LESS THAN(20))

For tables that are partitioned by using the RANGE partitioning method, PolarDB-X allows you to split a partition into two partitions based on a range.

Syntax

ALTER TABLE identifier SPLIT PARTITION identifier AT(number) INTO
 (PARTITION identifier,
  PARTITION identifier);

Example

In the tb1 table, you can split the p1 partition:

ALTER TABLE tb1 SPLIT PARTITION p1 AT(9) INTO (partition p11, partition p12)

The preceding statement has the same effect as the following statement:

ALTER TABLE tb1 SPLIT PARTITION p1 INTO
(PARTITION p11 VALUES LESS THAN(9),
PARTITION p12 VALUES LESS THAN(20));

Use the LIST or LIST COLUMN partitioning method to split a partition

Syntax

ALTER TABLE identifier SPLIT PARTITION identifier INTO
 (partition_definition,partition_definition, [, partition_definition] ...);

 partition_definition:
    PARTITION partition_name
        [VALUES IN (value_list | default)}]

Example
Execute the following SQL statement to create a table named tb1:
```
CREATE TABLE tb1(a int) PARTITION BY LIST(a)
 (PARTITION p1 VALUES IN(1, 2, 3, 4, 5, 6),
  PARTITION p2 VALUES IN(7,8,9),
  PARTITION p3 VALUES IN(default))
```
Execute the following SQL statement to split the p1 partition into three partitions named p10, p11, and p12:
```
ALTER TABLE tb1 SPLIT PARTITION p1 INTO
(PARTITION p10 VALUES IN (1,3,5),
PARTITION p11 VALUES IN (2,4),
PARTITION p12 VALUES IN (6))
```
The p3 partition is the default partition that contains all the rows in which the partition key values are not 1 to 9. PolarDB-X also allows you to split the default partition. Execute the following SQL statement to split the p3 partition and create partitions for rows in which the partition key values range from 10 to 15:
```
ALTER TABLE tb1 SPLIT PARTITION p3 INTO
(PARTITION p30 VALUES IN (10,11,12),
PARTITION p31 VALUES IN (13,14,15),
PARTITION p32 VALUES IN (default))
```

Use the HASH or KEY partitioning method to split a partition

Syntax

ALTER TABLE identifier SPLIT PARTITION identifier

Example
Execute the following SQL statement to create a table named tb1:
```
CREATE TABLE tb1(a int) PARTITION BY key(a) partitions 3;
```
The three partitions are named p1, p2, and p3 by default. You can execute the following statement to split the p1 partition into two partitions based on the range of hash values of the original p1 partition:
```
ALTER TABLE tb1 SPLIT PARTITION p1;
```

Distribute hot data to multiple partitions

For data skew in tables that are partitioned by using the KEY partitioning method, PolarDB-X allows you to evenly distribute the hot data to multiple partitions.

Syntax

ALTER TABLE identifier SPLIT INTO [newPartNamePrefix]
PARTITIONS partition_count BY HOT VALUE(value_list);

Example
Execute the following SQL statement to create a table named tb1:
```
CREATE TABLE tb1 (seller_id int, order_id int) 
PARTITION BY key (seller_id, order_id) PARTITIONS BY 3;
```
If the seller_id 88 is the ID of a big seller who has a large number of orders, you can execute the following SQL statement to evenly distribute the rows in which the value of the seller_id column is 88 to five new partitions: hp1, hp2, hp3, hp4, and hp5. The hot data is evenly distributed on the five partitions based on the hash values of the values in the column order_id.
```
ALTER TABLE tb1 SPLIT INTO hp PARTITIONS 5 BY HOT VALUE(88);
```

Extract hot data to a separate partition

For tables that are partitioned by using the KEY partitioning method, PolarDB-X allows you to extract hot data and store the hot data in a separate partition.

Syntax

ALTER TABLE identifier EXTRACT TO PARTITION [newPartNamePrefix] BY HOT VALUE(value_list);

Example
Execute the following SQL statement to create a table named tb1 where Column a and Column b are the partition key columns:
```
CREATE TABLE tb1 (a int, b int)
PARTITION BY key(a, b) PARTITIONS BY 3;
```
Execute the following SQL statement to extract rows in which the value in Column a is 88 and the value in Column b is 10 (a=88, b=10), and store the rows in a new partition:
```
ALTER TABLE tb1 EXTRACT TO PARTITION BY HOT VALUE(88,10);
```

For tables that are partitioned by using the LIST or LIST COLUMN partitioning method, PolarDB-X also allows you to extract the rows that contains a specific partition key value to a separate partition.

Syntax

ALTER TABLE identifier EXTRACT TO PARTITION pnew BY [HOT] VALUE(value_list);

Example

Execute the following SQL statement to create a table named tb1:

CREATE TABLE tb1(a int) PARTITION BY RANGE(a)
 (PARTITION p1 VALUES IN(1, 2, 3, 4, 5, 6),
  PARTITION p2 VALUES IN(7,8,9),
  PARTITION p3 VALUES IN(default))

Execute the following SQL statement to extract rows in which the value of Column a is 2 and store the rows in a separate partition:

ALTER TABLE tb1 EXTRACT TO PARTITION pnew BY HOT VALUE(2);

Merge partitions

PolarDB-X allows you to merge multiple partitions into one partition. You can merge partitions that are partitioned by using the HASH, KEY, RANGE or RANGE COLUMN partitioning method with adjacent partitions only. You can merge partitions that are partitioned by using the LIST or LIST COLUMN partitioning method.

Syntax

ALTER TABLE identifier MERGE PARTITIONS partition_name,...,partition_name TO partition_name;

Example
Execute the following SQL statement to create a table named tb1:
```
CREATE TABLE tb1(a int) PARTITION BY RANGE(a)
 (PARTITION p1 VALUES LESS THAN(20),
  PARTITION p2 VALUES LESS THAN(40),
 PARTITION p3 VALUES LESS THAN(100))
```
Execute the following SQL statement to merge partitions p1 and p2:
```
ALTER TABLE tb1 MERGE PARTITIONS p1,p2 to p12
```
Note Nonadjacent partitions that are partitioned by using the HASH, KEY, RANGE or RANGE COLUMN partitioning method cannot be merged. In this example, partition p1 cannot be merged with partition p3.

Migrate partitions

You can migrate a partition to a specified data node.

Syntax

ALTER TABLE identifier MOVE PARTITIONS partition_name[,partition_name,...] TO dn_id

Example

Execute the following SQL statement to create a table named tb1:

CREATE TABLE tb1(a int) PARTITION BY RANGE(a)
 (PARTITION p1 VALUES LESS THAN(20),
  PARTITION p2 VALUES LESS THAN(40),
  PARTITION p3 VALUES LESS THAN(100))

Execute the following SQL statement to migrate partitions p1 and p3 to data node DN2 (where DN2 is the ID of the data node):

ALTER TABLE tb1 MOVE PARTITIONS p1,p3 to 'DN2';

Add partitions

If the last partition of a table that is partitioned by using the RANGE or RANGE COLUMN partitioning method is not a catch-all partition (the last partition is not defined by VALUES LESS THAN (MAXVALUE)), you can add a partition after the last partition.

If the default partition is not defined in a table that is partitioned by using the LIST or LIST COLUMN partitioning method (no partition is defined by VALUES IN (DEFAULT)), you can add a partition to the table.

Partitions cannot be added to tables that are partitioned by using the KEY or HASH partitioning method.

You can use the following syntax to add one or more partitions to a table that is partitioned by using the RANGE or RANGE COLUMN partitioning method and does not have a catch-all partition, or a table that is partitioned by using the LIST or LIST COLUMN partitioning method and does not have a default partition:

Syntax

ALTER TABLE identifier ADD PARTITION (partition_definition [,partition_definition] ...)

Example

Execute the following SQL statement to create a table named tb1 that is partitioned by using the RANGE partitioning method:

CREATE TABLE tb1(a int) PARTITION BY RANGE(a)
 (PARTITION p1 VALUES LESS THAN(20),
  PARTITION p2 VALUES LESS THAN(40))

Execute the following SQL statement to add two partitions to table tb1:

ALTER TABLE tb1 ADD PARTITION (PARTITION p3 VALUES LESS THAN(60),
                              PARTITION p4 VALUES LESS THAN(80))

Delete partitions

You can delete partitions of a table that is partitioned by using the RANGE, RANGE COLUMN, LIST, or LIST COLUMN partitioning method.

Syntax

ALTER TABLE identifier DROP PARTITION partitiion_name [,partition_name] ...

Example

Execute the following SQL statement to create a table named tb1 that is partitioned by using the RANGE partitioning method:

CREATE TABLE tb1(a int) PARTITION BY RANGE(a)
 (PARTITION p1 VALUES LESS THAN(20),
  PARTITION p2 VALUES LESS THAN(40),
  PARTITION p3 VALUES LESS THAN(60))

Execute the following SQL statement to delete partition p3:

ALTER TABLE tb1 DROP PARTITION p3

Rename partitions

You can use the following syntax to rename an existing partition.

Syntax

ALTER TABLE identifier RENAME PARTITION old_part_name to new_part_name[, old_part_name to new_part_name]

Example
Execute the following SQL statement to create a table named tb1 that is partitioned by using the RANGE partitioning method:
```
CREATE TABLE tb1(a int) PARTITION BY RANGE(a)
 (PARTITION p1 VALUES LESS THAN(20),
  PARTITION p2 VALUES LESS THAN(40),
  PARTITION p3 VALUES LESS THAN(60))
```
Execute the following SQL statement to change the names of partitions p2 and p3 to p20 and p30:
```
ALTER TABLE tb1 RENAME PARTITION p2 to p20, p3 to p30
```

Change column values in partitions

You can use the following syntax to modify column values in a partition of a table that is partitioned by using the LIST or LIST COLUMN partitioning method.

Syntax

ALTER TABLE identifier MODIFY PARTITION partition_name ADD/DROP VALUES (value_list)

Example

Execute the following SQL statement to create a table named tb1 that is partitioned by using the LIST partitioning method:

CREATE TABLE tb1(a int) PARTITION BY List(a)
 (PARTITION p1 VALUES IN(1, 2, 3, 4, 5, 6),
  PARTITION p2 VALUES IN(7,8,9),
  PARTITION p3 VALUES IN(10,11,12))

Execute the following SQL statement to delete the value 9 from partition p2 and add the values 13 and 14 to partition p3:

ALTER TABLE tb1 MODIFY PARTITION p2 DROP VALUES(9);
ALTER TABLE tb1 MODIFY PARTITION p2 ADD VALUES(13,14);

Modify the partitions of a global secondary index table

You can modify the partitions of a global secondary index (GSI) table by using the preceding statements and replacing the table name with the logical name of the GSI.

Example

Execute the following SQL statement to create a table named tb1:

CREATE TABLE tb1(a int, b int, global index g1(b) partition by key(b) partitions 3)
PARTITION BY RANGE(a)
 (PARTITION p1 VALUES LESS THAN(20),
  PARTITION p2 VALUES LESS THAN(40),
  PARTITION p3 VALUES LESS THAN(60))

The table tb1 has a GSI named g1. The logical name of g1 is tb1.g1. You can execute the following SQL statement to split the p1 partition of the GSI table:

ALTER TABLE tb1.g1 SPLIT PARTITION p1