Manage a partitioned table|V4.0.0| docs|Distributed Database

Manage a partitioned table

Last Updated：2023-07-21 09:11:01 Updated

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

Support for partition management operations

Partitioning types	Add a partition	Drop a partition	Truncate a partition
RANGE partitioning	Yes	Yes	Yes
RANGE COLUMNS	Yes	Yes	Yes
LIST partitioning	Yes	Yes	Yes
LIST COLUMNS partitioning	Yes	Yes	Yes
HASH partitioning	Not supported	Not supported	Not supported
KEY partitioning	Not supported	Not supported	Not supported

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.

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

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

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.

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

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

View the results.

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