Add a partition

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

Support for adding partitions to partitioned tables

Take note of the following points in the Oracle mode of OceanBase Database:

• You can add partitions only to RANGE- and LIST-partitioned tables. You cannot add partitions to HASH partitioned tables.

• In the case of subpartitioned tables, you can add partitions only to RANGE-LIST-partitioned tables.

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

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:
  range_partition_option | list_partition_option

range_partition_option:
  partition_name VALUES LESS THAN partition_expr

list_partition_option:
  partition_name VALUES partition_expr

Here are some examples:

• Create a RANGE-partitioned table named tbl1_r and add the M202006 partition to the table.

  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 the p4 partition to the table.

  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 RANGE-RANGE-subpartitioned table named t2_m_rr by using a template, and then add the p2 partition to the table.

  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 SQL syntax for adding a partition to a non-template-based subpartitioned table is as follows:

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

Example: Create a RANGE-RANGE-subpartitioned table named t2_f_rr by using a template, and then add the p2 partition to the table.

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

In the Oracle mode of OceanBase Database, you can add a subpartition only to a non-template-based subpartitioned table.

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 list_partition_expr, ...}

Parameters

Example

Create a non-template-based RANGE-RANGE-subpartitioned table named t2_f_rr, and then add the sp5 and sp6 subpartitions to the p1 partition of the table.

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 MODIFY PARTITION p1 ADD
       SUBPARTITION sp5 VALUES LESS THAN(2023),
       SUBPARTITION sp6 VALUES LESS THAN(2024);
Query OK, 0 rows affected