After creating a partitioned table, you can add partitions to it.
Support for adding partitions to partitioned tables
The following table describes the support for adding partitions to partitioned and subpartitioned tables.
Partitioned table type |
Partitioning type |
Add partitions |
Add subpartitions |
|---|---|---|---|
| Partitioned table |
|
Supported | - |
| Partitioned table |
|
Not supported | - |
| Subpartitioned table |
|
Supported | Supported |
| Subpartitioned table |
|
Supported | Not supported |
| Subpartitioned table |
|
Supported | Supported |
| Subpartitioned table |
|
Supported | Not supported |
| Subpartitioned table |
|
Supported | Supported |
| Subpartitioned table |
|
Supported | Not supported |
| Subpartitioned table |
|
Supported | Supported |
| Subpartitioned table |
|
Supported | Not supported |
| Subpartitioned table |
|
Not supported | Supported |
| Subpartitioned table |
|
Not supported | Not supported |
| Subpartitioned table |
|
Not supported | Supported |
| Subpartitioned table |
|
Not supported | Not supported |
Add a partition to a partitioned table
You can add a partition to a partitioned table, a template-based subpartitioned table, or a non-template-based subpartitioned table.
Add a partition to a partitioned table or a template-based subpartitioned table
The SQL syntax for adding a partition to a partitioned table or a template-based subpartitioned table is as follows:
ALTER TABLE table_name ADD PARTITION (partition_option);
partition_option:
{PARTITION partition_name VALUES LESS THAN range_partition_expr
[,PARTITION partition_name VALUES LESS THAN range_partition_expr]... }
|{PARTITION partition_name VALUES IN list_partition_expr
[,PARTITION partition_name VALUES IN list_partition_expr]...}
Note
For a RANGE- or RANGE COLUMNS-partitioned table, you can add a partition only after the largest partition, rather than at the start or in the middle. If a table has a
MAXVALUEpartition, you cannot add new partitions to the table.For a LIST- or LIST COLUMNS-partitioned table, you cannot add a partition that conflicts with previous partitions. If a LIST- or LIST COLUMNS-partitioned table has a
DEFAULTpartition, you cannot add new partitions to the table.Adding a partition to a RANGE-, RANGE COLUMNS-, LIST, or LIST COLUMNS-partitioned table does not affect the use of global and local indexes.
For a template-based subpartitioned table, you only need to specify the partition definition when you add a partition. The subpartition definition is automatically specified based on the template.
Examples:
Create a RANGE-partitioned table named
tbl1_rand add theM202106partition to the table.obclient> CREATE TABLE tbl1_r (id INT NOT NULL,value varchar(50),time TIMESTAMP NOT NULL) PARTITION BY RANGE(UNIX_TIMESTAMP(time)) (PARTITION M202101 VALUES LESS THAN(UNIX_TIMESTAMP('2021/02/01')), PARTITION M202102 VALUES LESS THAN(UNIX_TIMESTAMP('2021/03/01')), PARTITION M202103 VALUES LESS THAN(UNIX_TIMESTAMP('2021/04/01')), PARTITION M202104 VALUES LESS THAN(UNIX_TIMESTAMP('2021/05/01')), PARTITION M202105 VALUES LESS THAN(UNIX_TIMESTAMP('2021/06/01')) ); Query OK, 0 rows affected obclient> ALTER TABLE tbl1_r ADD PARTITION (PARTITION M202106 VALUES LESS THAN(UNIX_TIMESTAMP('2021/07/01'))); Query OK, 0 rows affectedCreate a LIST-partitioned table named
tbl1_land add thep2andp3partitions to the table.obclient> CREATE TABLE tbl1_l (col1 INT PRIMARY KEY,col2 VARCHAR(50)) PARTITION BY LIST(col1) (PARTITION p0 VALUES IN (1,2,3), PARTITION p1 VALUES IN (5,6) ); Query OK, 0 rows affected obclient> ALTER TABLE tbl1_l ADD PARTITION (PARTITION p2 VALUES IN (7,8), PARTITION p3 VALUES IN (DEFAULT) ); Query OK, 0 rows affectedAdd the
p3andp4partitions to the template-based RANGE COLUMNS + RANGE COLUMNS subpartitioned tablet_m_rcrc.obclient> CREATE TABLE t_m_rcrc(col1 INT,col2 INT) PARTITION BY RANGE COLUMNS(col1) SUBPARTITION BY RANGE COLUMNS(col2) SUBPARTITION TEMPLATE (SUBPARTITION mp0 VALUES LESS THAN(1000), SUBPARTITION mp1 VALUES LESS THAN(2000), SUBPARTITION mp2 VALUES LESS THAN(3000) ) (PARTITION p0 VALUES LESS THAN(100), PARTITION p1 VALUES LESS THAN(200), PARTITION p2 VALUES LESS THAN(300) ); Query OK, 0 rows affected obclient> ALTER TABLE t_m_rcrc ADD PARTITION (PARTITION p3 VALUES LESS THAN(400), PARTITION p4 VALUES LESS THAN(500) ); Query OK, 0 rows affected
Add a partition to a non-template-based subpartitioned table
The SQL syntax for adding a partition to a non-template-based subpartitioned table is as follows:
ALTER TABLE table_name ADD PARTITION (partition_option);
partition_option:
{PARTITION partition_name VALUES LESS THAN range_partition_expr (subpartition_option)
[,PARTITION partition_name VALUES LESS THAN range_partition_expr (subpartition_option)]... }
|{PARTITION partition_name VALUES IN list_partition_expr (subpartition_option)
[,PARTITION partition_name VALUES IN list_partition_expr (subpartition_option)]...}
subpartition_option:
{SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
|{SUBPARTITION subpartition_name VALUES IN list_partition_expr, ....}
|{SUBPARTITION subpartition_name, ....}
Note
For a non-template-based subpartitioned table, when adding a partition, you must specify both the definition of the partition and the definitions of the subpartitions within that partition.
The following example shows how to add a partition p2 to the non-template-based RANGE + RANGE subpartitioned table t_f_rclc:
obclient> CREATE TABLE t_f_rclc (col1 INT,col2 INT)
PARTITION BY RANGE COLUMNS(col1)
SUBPARTITION BY LIST COLUMNS(col2)
(PARTITION p0 VALUES LESS THAN(100)
(SUBPARTITION sp0 VALUES IN(1,3),
SUBPARTITION sp1 VALUES IN(4,6),
SUBPARTITION sp2 VALUES IN(7,9)),
PARTITION p1 VALUES LESS THAN(200)
(SUBPARTITION sp3 VALUES IN(1,3),
SUBPARTITION sp4 VALUES IN(4,6),
SUBPARTITION sp5 VALUES IN(7,9))
);
obclient> ALTER TABLE t_f_rclc ADD PARTITION
(PARTITION p2 VALUES LESS THAN(300)
(SUBPARTITION sp6 VALUES IN(1,3),
SUBPARTITION sp7 VALUES IN(4,6),
SUBPARTITION sp8 VALUES IN(7,9))
);
Query OK, 0 rows affected
Add a subpartition
Notice
- You cannot add a subpartition to a table whose subpartitioning type is HASH or KEY.
- The name of the new subpartition must be unique among all subpartitions in the table and any subpartitions of the parent partition.
- When adding a RANGE subpartition, the upper bound of the new range must be greater than the upper bound of the largest existing subpartition in the parent partition. When adding multiple RANGE subpartitions, their upper bounds must be in ascending order, with the largest range subpartition's upper bound appearing last.
- When adding a LIST subpartition, its value must not duplicate any value of an existing subpartition in the parent partition.
- The operation of adding multiple subpartitions is atomic: either all succeed or none succeed.
Syntax
ALTER TABLE table_name MODIFY PARTITION partition_name
ADD SUBPARTITION (subpartition_option);
subpartition_option:
{SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr
[, SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr] ...}
| {SUBPARTITION subpartition_name VALUES IN list_partition_expr
[, SUBPARTITION subpartition_name VALUES IN list_partition_expr] ...}
Parameters
Parameter |
Description |
|---|---|
| table_name | Specifies the table name. |
| partition_name | Specifies the name of the primary partition to which the subpartition to be added belongs. |
| subpartition_name | Specifies the name of the subpartition to be added. |
| subpartition_option | Indicates the newly added subpartition definition. |
| range_partition_expr | Represents a RANGE or RANGE COLUMNS partitioning expression. |
| list_partition_expr | Represents the partitioning expression for LIST or LIST COLUMNS. |
Examples
Add a subpartition to a template-based RANGE + RANGE subpartitioned table.
Create a template-based subpartitioned table
tbl1_m_rrwith RANGE + RANGE partitions.CREATE TABLE tbl1_m_rr (col1 INT, col2 INT, col3 INT) PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2) SUBPARTITION TEMPLATE (SUBPARTITION p0 VALUES LESS THAN (1000), SUBPARTITION p1 VALUES LESS THAN (2000)) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200));Add a subpartition
p1sp2to the partitionp1of the tabletbl1_m_rr.ALTER TABLE tbl1_m_rr MODIFY PARTITION p1 ADD SUBPARTITION (SUBPARTITION p1sp2 VALUES LESS THAN (3000));
Add a subpartition to a non-template-based RANGE + RANGE subpartitioned table.
Create a non-template-based subpartitioned table
tbl2_f_rrwith RANGE + RANGE partitions.CREATE TABLE tbl2_f_rr(col1 INT, col2 INT) PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2) (PARTITION p0 VALUES LESS THAN(100) (SUBPARTITION sp0 VALUES LESS THAN(2020), SUBPARTITION sp1 VALUES LESS THAN(2021) ), PARTITION p1 VALUES LESS THAN(200) (SUBPARTITION sp2 VALUES LESS THAN(2020), SUBPARTITION sp3 VALUES LESS THAN(2021), SUBPARTITION sp4 VALUES LESS THAN(2022) ) );Add subpartitions
sp5andsp6to the partitionp1of the tabletbl2_f_rr.ALTER TABLE tbl2_f_rr MODIFY PARTITION p1 ADD SUBPARTITION (SUBPARTITION sp5 VALUES LESS THAN(2023), SUBPARTITION sp6 VALUES LESS THAN(2024));