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 named tbl1_r and add the M197106 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 M197101 VALUES LESS THAN(UNIX_TIMESTAMP('1971/02/01')),
           PARTITION M197102 VALUES LESS THAN(UNIX_TIMESTAMP('1971/03/01')),
           PARTITION M197103 VALUES LESS THAN(UNIX_TIMESTAMP('1971/04/01')),
           PARTITION M197104 VALUES LESS THAN(UNIX_TIMESTAMP('1971/05/01')),
           PARTITION M197105 VALUES LESS THAN(UNIX_TIMESTAMP('1971/06/01'))
          );
  Query OK, 0 rows affected
  

  Add the M197106 partition to the tbl1_r table.

  obclient> ALTER TABLE tbl1_r ADD PARTITION
         (PARTITION M197106 VALUES LESS THAN(UNIX_TIMESTAMP('1971/07/01')));
  Query OK, 0 rows affected
  

  View the added M197106 partition in the tbl1_r table.

  obclient> show create table tbl1_r\G
  *************************** 1. row ***************************
         Table: tbl1_r
  Create Table: CREATE TABLE `tbl1_r` (
    `id` int(11) NOT NULL,
    `value` varchar(50) DEFAULT NULL,
    `time` timestamp NOT NULL
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by range(UNIX_TIMESTAMP(time))
  (partition M197101 values less than (1612108800),
  partition M197102 values less than (1614528000),
  partition M197103 values less than (1617206400),
  partition M197104 values less than (1619798400),
  partition M197105 values less than (1622476800),
  partition M197106 values less than (1625068800))
  1 row in set
  

  Insert data into the M197106 partition of the tbl1_r table.

  obclient> INSERT INTO tbl1_r VALUES (1,'1','1971/06/02');
  Query OK, 1 row affected
  

  View the M197106 partition into which data is inserted.

  obclient> SELECT * FROM tbl1_r partition(M197106);
  +----+-------+---------------------+
  | id | value | time                |
  +----+-------+---------------------+
  |  1 | 1     | 1971-06-02 00:00:00 |
  +----+-------+---------------------+
  1 row in set
  

• Add a partition to a LIST-partitioned table. Create a LIST-partitioned table named tbl1_l2 and add partitions p2 and p3 to the table.

  obclient> CREATE TABLE tbl1_l2 (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
  

  Add the p2 and p3 partitions to the tbl1_l2 table.

  obclient> ALTER TABLE tbl1_l2 ADD PARTITION
         (PARTITION p2 VALUES IN (7,8),
          PARTITION p3  VALUES IN (DEFAULT)
         );
  Query OK, 0 rows affected
  

  View the p2 and p3 partitions in the tbl1_l2 table.

  obclient> show create table tbl1_l2\G
  *************************** 1. row ***************************
         Table: tbl1_l2
  Create Table: CREATE TABLE `tbl1_l2` (
    `col1` int(11) NOT NULL,
    `col2` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`col1`)
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by list(col1)
  (partition p0 values in (1,2,3),
  partition p1 values in (5,6),
  partition p2 values in (7,8),
  partition p3 values in (DEFAULT))
  1 row in set
  

  Insert data into the p2 and p3 partitions of the tbl1_l2 table.

  obclient> insert into tbl1_l2 values(8,'8'), (9,'9');
  Query OK, 2 rows affected (0.01 sec)
  Records: 2  Duplicates: 0  Warnings: 0
  

  View the data inserted into the p2 and p3 partitions.

  obclient> SELECT * FROM tbl1_l2 partition(p2,p3);
  +------+------+
  | col1 | col2 |
  +------+------+
  |    8 | 8    |
  |    9 | 9    |
  +------+------+
  2 rows in set
  

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 the partitions in a table.
• Before dropping a partition, ensure that there are no active transactions or queries 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 M197105 and M197106 from the partitioned table tbl1_r.

1. View the tbl1_r partitioned table.

   obclient> show create table tbl1_r\G
   *************************** 1. row ***************************
          Table: tbl1_r
   Create Table: CREATE TABLE `tbl1_r` (
     `id` int(11) NOT NULL,
     `value` varchar(50) DEFAULT NULL,
     `time` timestamp NOT NULL
   ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
    partition by range(UNIX_TIMESTAMP(time))
   (partition M197101 values less than (1612108800),
   partition M197102 values less than (1614528000),
   partition M197103 values less than (1617206400),
   partition M197104 values less than (1619798400),
   partition M197105 values less than (1622476800),
   partition M197106 values less than (1625068800))
   1 row in set
   

2. Drop partitions M197105 and M197106 from the partitioned table tbl1_r.

   obclient> ALTER TABLE tbl1_r DROP PARTITION M197105,M197106;
   Query OK, 0 rows affected
   

3. View the results.

   obclient>show create table tbl1_r\G
   *************************** 1. row ***************************
          Table: tbl1_r
   Create Table: CREATE TABLE `tbl1_r` (
     `id` int(11) NOT NULL,
     `value` varchar(50) DEFAULT NULL,
     `time` timestamp NOT NULL
   ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
    partition by range(UNIX_TIMESTAMP(time))
   (partition M197101 values less than (1612108800),
   partition M197102 values less than (1614528000),
   partition M197103 values less than (1617206400),
   partition M197104 values less than (1619798400))
   1 row in set
   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 there are no active transactions or queries in this partition. Otherwise, SQL statement errors or exceptions may occur.

Examples

Clear the data in the M197101 and M197102 partitions of the tbl1_r partitioned table.

1. Query the data in the M197101 and M197102 partitions of the tbl1_r partitioned table.

   obclient>  select * from tbl1_r partition(M197101,M197102);
   +----+--------+---------------------+
   | id | value  | time                |
   +----+--------+---------------------+
   |  1 | Zhang San   | 0000-00-00 00:00:00 |
   |  2 | Li Si   | 0000-00-00 00:00:00 |
   +----+--------+---------------------+
   2 rows in set
   

2. Clear the data in the M197101 and M197102 partitions of the tbl1_r partitioned table.

   obclient> ALTER TABLE tbl1_r TRUNCATE PARTITION M197101,M197102;
   Query OK, 0 rows affected
   

3. View the results.

   obclient>  select * from tbl1_r partition(M197101,M197102);
   Empty set