Add a partition

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.

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

SQL syntax for adding a partition to a partitioned table and a template-based subpartitioned table:

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

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 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

SQL syntax for adding a partition to a non-template-based subpartitioned table:

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

Create 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

OceanBase Database in MySQL mode does not allow you to add subpartitions to a table.