Add a partition|V4.2.5|OceanBase Database| docs|Distributed Database

Add a partition

Last Updated：2026-03-18 06:13:32 Updated

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

Support for adding partitions to partitioned tables

In OceanBase Database's Oracle mode:

For a partitioned table, you can add partitions to a RANGE partition or a LIST partition, but not to a HASH partition.
For a subpartitioned table, you can add partitions to a RANGE or LIST subpartition.

The following table shows the support for adding partitions to partitioned tables.

Partitioned table	Partition type	Add a partition to the partitioned table	Add a partition to the subpartitioned table
Partitioned table	RANGE / LIST	Supported	-
Partitioned table	HASH	Not supported	-
Subpartitioned table	RANGE + RANGE / RANGE + LIST	Supported	Supported
Subpartitioned table	RANGE + HASH	Supported	Not supported
Subpartitioned table	LIST + RANGE / LIST + LIST	Supported	Supported
Subpartitioned table	LIST + HASH	Supported	Not supported
Subpartitioned table	HASH + RANGE / HASH + LIST	Not supported	Supported
Subpartitioned table	HASH + HASH	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 following example shows how to add a partition to a partitioned table or a template-based subpartitioned table:

ALTER TABLE table_name ADD PARTITION partition_option;

partition_option:
  range_partition_option | list_partition_option

range_partition_option:
  partition_name VALUES LESS THAN partition_expr

list_partition_option:
  partition_name VALUES partition_expr

Note

For a RANGE partition, you can add a partition only after the largest partition. You cannot add a partition in the middle or at the beginning. If the current partition contains a MAXVALUE partition, you cannot add any more partitions.
When you add a partition to a LIST partition, the new partition must not conflict with the existing partitions. If a LIST partition has a default partition, you cannot add any partitions to it.
Adding a partition to a RANGE or LIST partition does not affect the use of global indexes and local indexes.
For a template-based subpartitioned table, you only need to specify the definition of the partition when adding a partition. The subpartition definitions will be automatically filled in based on the template.

Here are some examples:

Create a RANGE partitioned table named tbl1_r and add a partition named M202006.

obclient> CREATE TABLE tbl1_r(log_id INT,log_date DATE NOT NULL DEFAULT SYSDATE)
PARTITION BY RANGE(log_date) 
(PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
, PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
, PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
, PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
, PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
);
Query OK, 0 rows affected

obclient> ALTER TABLE tbl1_r ADD PARTITION
       M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD'));
Query OK, 0 rows affected

Create a LIST partitioned table named tbl1_l and add a partition named p4.

obclient> CREATE TABLE tbl1_l(log_id INT,log_value VARCHAR2(20))
       PARTITION BY LIST(log_value) 
        (PARTITION p1 VALUES ('A'),
         PARTITION p2 VALUES ( 'B' ),
         PARTITION p3 VALUES ( 'C' )
        );
Query OK, 0 rows affected

obclient> ALTER TABLE tbl1_l ADD PARTITION p4 VALUES('D');
Query OK, 0 rows affected

Create a template-based subpartitioned table named t2_m_rr with RANGE + RANGE partitions and add a partition named p2.

obclient> CREATE TABLE t2_m_rr(col1 INT,col2 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES LESS THAN(2020),
          SUBPARTITION mp1 VALUES LESS THAN(2021),
          SUBPARTITION mp2 VALUES LESS THAN(2022)
          )
         (PARTITION p0 VALUES LESS THAN(100),
          PARTITION p1 VALUES LESS THAN(200)
          );
Query OK, 0 rows affected

obclient> ALTER TABLE t2_m_rr ADD PARTITION p2 VALUES LESS THAN(300); 
Query OK, 0 rows affected

Add a partition to a non-template-based subpartitioned table

The following example shows how to add a partition to a non-template-based subpartitioned table:

ALTER TABLE table_name ADD PARTITION partition_option (subpartition_option);

partition_option:
  range_partition_option | list_partition_option

range_partition_option:
  partition_name VALUES LESS THAN partition_expr

list_partition_option:
  partition_name VALUES partition_expr

subpartition_option:
  {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
 |{SUBPARTITION subpartition_name VALUES list_partition_expr, ....}
 |{SUBPARTITION subpartition_name, ....}

Note

For a non-template-based subpartitioned table, you need to specify both the definition of the partition and the definitions of the subpartitions under it when adding a partition.

Here is an example of creating a non-template-based subpartitioned table named t2_f_rr with RANGE + RANGE partitions and adding a partition named p2:

obclient> CREATE TABLE t2_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)
           )
         );
Query OK, 0 rows affected

obclient> ALTER TABLE t2_f_rr ADD PARTITION p2 VALUES LESS THAN(300)
       (SUBPARTITION sp5 VALUES LESS THAN(2020),
        SUBPARTITION sp6 VALUES LESS THAN(2021),
        SUBPARTITION sp7 VALUES LESS THAN(2022)
        ); 
Query OK, 0 rows affected

Add a subpartition

Notice

Subpartitions of the HASH type cannot be added.
The subpartition name must be unique in the table.
When you add a RANGE subpartition, the upper bound of the RANGE subpartition must be greater than the upper bound of the largest partition in the corresponding partition. When you add multiple RANGE subpartitions, the upper bounds must be in ascending order.
When you add a LIST subpartition, the subpartition value must be unique in the corresponding partition.
Adding multiple subpartitions is an atomic operation. All subpartitions are added or none of them is added.

Syntax

ALTER TABLE table_name MODIFY PARTITION partition_name ADD subpartition_list;

subpartition_list:
    {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
    | {SUBPARTITION subpartition_name VALUES list_partition_expr, ...}

Parameters

Parameter	Description
table_name	The name of the table.
partition_name	The name of the partition to which the subpartition belongs.
subpartition_name	The name of the subpartition to be added.

Examples

Add subpartitions to a partitioned table.

Create a partitioned table tbl_m2_rr with RANGE + RANGE subpartitioning, and add a partition p2 to the table.

obclient> CREATE TABLE tbl_m2_rr(col1 INT,col2 INT)
    PARTITION BY RANGE(col1)
    SUBPARTITION BY RANGE(col2)
    SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES LESS THAN(2020),
        SUBPARTITION mp1 VALUES LESS THAN(2021),
        SUBPARTITION mp2 VALUES LESS THAN(2022))
      (PARTITION p0 VALUES LESS THAN(100),
      PARTITION p1 VALUES LESS THAN(200));

Add subpartitions sp3 and sp4 to the partition p1.

obclient> ALTER TABLE tbl_m2_rr MODIFY PARTITION p1 ADD
    SUBPARTITION sp3 VALUES LESS THAN(2023),
    SUBPARTITION sp4 VALUES LESS THAN(2024);

Query the information about the table tbl_m2_rr.

obclient> SHOW CREATE TABLE tbl_m2_rr;

The return result is as follows:

+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE     | CREATE TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TBL_M2_RR | CREATE TABLE "TBL_M2_RR" (
  "COL1" NUMBER(*,0),
  "COL2" NUMBER(*,0)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by range("COL1") subpartition by range("COL2")
(partition "P0" values less than (100) (
subpartition "P0SMP0" values less than (2020),
subpartition "P0SMP1" values less than (2021),
subpartition "P0SMP2" values less than (2022)),
partition "P1" values less than (200) (
subpartition "P1SMP0" values less than (2020),
subpartition "P1SMP1" values less than (2021),
subpartition "P1SMP2" values less than (2022),
subpartition "SP3" values less than (2023),
subpartition "SP4" values less than (2024))) |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Add subpartitions to a non-partitioned table.

Create a non-partitioned table tbl_f2_rr with RANGE + RANGE subpartitioning.

obclient> CREATE TABLE tbl_f2_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.

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

Query the information about the table tbl_f2_rr.

obclient> SHOW CREATE TABLE tbl_f2_rr;

The return result is as follows:

+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE     | CREATE TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TBL_F2_RR | CREATE TABLE "TBL_F2_RR" (
  "COL1" NUMBER(*,0),
  "COL2" NUMBER(*,0)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
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),
subpartition "SP5" values less than (2023),
subpartition "SP6" values less than (2024))) |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set