Add a partition|V4.3.5| docs|Distributed Database

Add a partition

Last Updated：2025-06-25 08:02:52 Updated

After you create a partitioned table, you can add partitions to the table.

Support for adding partitions to partitioned tables

The following table describes the support for adding partitions to partitioned and subpartitioned tables in MySQL mode of OceanBase Database.

Partitioned table type	Partitioning type	Add partitions	Add subpartitions
Partitioned table	RANGE RANGE COLUMNS LIST LIST COLUMNS	Supported	-
Partitioned table	HASH KEY	Not supported	-
Subpartitioned table	RANGE-RANGE RANGE-RANGE COLUMNS RANGE-LIST RANGE-LIST COLUMNS RANGE-HASH RANGE-KEY	Supported	Supported
Subpartitioned table	RANGE + HASH RANGE + KEY	Supported	Not supported
Subpartitioned table	RANGE COLUMNS-RANGE RANGE COLUMNS-RANGE COLUMNS RANGE COLUMNS-LIST RANGE COLUMNS-LIST COLUMNS	Supported	Supported
Subpartitioned table	RANGE COLUMNS + HASH RANGE COLUMNS + KEY	Supported	Not supported
Subpartitioned table	LIST + RANGE LIST + RANGE COLUMNS LIST + LIST LIST + LIST COLUMNS	Supported	Supported
Subpartitioned table	LIST + HASH LIST + KEY	Supported	Not supported
Subpartitioned table	LIST COLUMNS + RANGE LIST COLUMNS + RANGE COLUMNS LIST COLUMNS + LIST LIST COLUMNS + LIST COLUMNS	Supported	Supported
Subpartitioned table	LIST COLUMNS + HASH LIST COLUMNS + KEY	Supported	Not supported
Subpartitioned table	HASH + RANGE HASH + RANGE COLUMNS HASH + LIST HASH + LIST COLUMNS	Not supported	Supported
Subpartitioned table	HASH + HASH HASH + KEY	Not supported	Not supported
Subpartitioned table	KEY + RANGE KEY + RANGE COLUMNS KEY + LIST KEY + LIST COLUMNS	Not supported	Supported
Subpartitioned table	KEY + HASH KEY + KEY	Not supported	Not supported

Add a partition

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 and a template-based subpartitioned table

The SQL syntax for adding a partition to a partitioned table and 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 MAXVALUE partition, 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 DEFAULT partition, 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.

Here are some examples:

Create a RANGE-partitioned table tbl1_r and add the M202106 partition 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 affected

Create a LIST-partitioned table named tbl1_l and add the p2 and p3 partitions 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 affected

Create a template-based RANGE COLUMNS-RANGE COLUMNS-subpartitioned table named t_m_rcrc and add the p3 and p4 partitions to it.

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, you must specify both partition and subpartition definitions when you add a partition.

Here is an example of creating a non-template-based RANGE-RANGE-subpartitioned table named t_f_rclc and add the p2 partition to it:

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

Adding new subpartitions is not supported for scenarios where the subpartition type is HASH or KEY.
In OceanBase Database V4.3.5, support for adding new subpartitions is available starting from V4.3.5 BP1.
The added subpartition cannot have the same name as any existing primary or subpartition in the table.
When adding Range subpartitions, the upper bound of the Range must be greater than the upper bound of the largest current subpartition under the primary partition. When adding multiple Range subpartitions, the upper bounds must be in ascending order, with larger upper bounds written later.
When adding List subpartitions, the values cannot duplicate those of existing subpartitions under the current primary partition.
The operation of adding multiple subpartitions is atomic; it either succeeds completely or fails completely.

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] ...}

Parameter explanation

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	Indicates the Range/Range Columns partition expression.
list_partition_expr	Indicates the List/List Columns partition expression.

Example

Add a subpartition table to the Range + Range templated partition table.

Create a templated subpartition table tbl1_m_rr with Range + Range partitioning.

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 the subpartition p1sp2 to the partition p1 of table tbl1_m_rr.

ALTER TABLE tbl1_m_rr MODIFY PARTITION p1
    ADD SUBPARTITION (SUBPARTITION p1sp2 VALUES LESS THAN (3000));

Add a subpartition table to the non-templated partition table in the Range + Range direction.

Create a non-templated subpartition table tbl2_f_rr with Range + Range partitioning.

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 sp5 and sp6 to the partition p1 of table tbl2_f_rr.

ALTER TABLE tbl2_f_rr MODIFY PARTITION p1
    ADD SUBPARTITION
       (SUBPARTITION sp5 VALUES LESS THAN(2023),
        SUBPARTITION sp6 VALUES LESS THAN(2024));

References

ALTER TABLE