Manage a partitioned table

After you create a partitioned table, you can add, drop, or truncate partitions in the partitioned table.

Support for partition management operations

Add a partition

Syntax

ALTER TABLE table_name ADD PARTITION (partition_option);

partition_option:
  range_partition_option | list_partition_option

range_partition_option:
  ( PARTITION partition_name VALUES LESS THAN partition_expr
   [, PARTITION partition_name VALUES LESS THAN partition_expr]...
  )

list_partition_option:
  (PARTITION partition_name VALUES IN partition_expr
    [, PARTITION partition_name VALUES IN 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.

Examples

• Add a partition to a RANGE-partitioned table. 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
  

• Add a partition to a LIST-partitioned table. 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
  

Drop a partition

Syntax

ALTER TABLE table_name DROP PARTITION partition_name_list;

partition_name_list:
  partition_name [, partition_name ...]

Note

• You can drop multiple partitions at a time, but you cannot drop all partitions in a table.

• Before dropping a partition, ensure that no active transaction or query exists in this partition. Otherwise, SQL statement errors or exceptions may occur.

• When you drop a partition, the data in the partition is also dropped. If you want to drop only the data, use the TRUNCATE statement.

Examples

Drop partitions M202105 and M202106 from the partitioned table tbl1_r.

obclient> ALTER TABLE tbl1_r DROP PARTITION M202105,M202106;
Query OK, 0 rows affected

Truncate a partition

Syntax

ALTER TABLE table_name TRUNCATE PARTITION partition_name_list;

partition_name_list:
  partition_name [, partition_name ...]

Note

• You can clear the data in one or more partitions by using the TRUNCATE statement.

• Before truncating a partition, ensure that no active transaction or query exists in this partition. Otherwise, SQL statement errors or exceptions may occur.

Examples

Clear the data in partitions M202101 and M202102 of the partitioned table tbl1_r.

obclient> ALTER TABLE tbl1_r TRUNCATE PARTITION M202101,M202102;
Query OK, 0 rows affected