This topic describes how to perform partition operations, including re-partitioning, adding partitions, dropping partitions, and truncating partitions in the MySQL mode of OceanBase Database.
Re-partitioning
The syntax of the ALTER TABLE ... REORGANIZE PARTITION statement is as follows:
ALTER TABLE table_name PARTITION BY (partition_definitions);
Examples
Example 1: Change a hash-partitioned table to a key-partitioned table
obclient> CREATE TABLE tbl2(c1 INT, c2 DATETIME, PRIMARY KEY(c1, c2))
PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> ALTER TABLE tbl2 PARTITION BY KEY(c1) PARTITIONS 10;
Query OK, 0 rows affected
Example 2: Change a hash-partitioned table to a hash-range-partitioned table
obclient> ALTER TABLE tbl2
PARTITION BY HASH(c1)
SUBPARTITION BY RANGE COLUMNS(c2)
SUBPARTITION TEMPLATE(
SUBPARTITION p1 VALUES LESS THAN ('2016-10-10'),
SUBPARTITION p2 VALUES LESS THAN ('2116-3-30')) PARTITIONS 2;
Query OK, 0 rows affected
Example 3: Change a hash-range-partitioned table to a range-columns-range-columns-partitioned table
obclient> ALTER TABLE tbl2
PARTITION BY RANGE COLUMNS(c1)
SUBPARTITION BY RANGE COLUMNS(c2)
(
PARTITION p0 VALUES LESS THAN (100)
(SUBPARTITION sp0 VALUES LESS THAN ('2020-01-01')
,SUBPARTITION sp1 VALUES LESS THAN ('2021-01-01')
,SUBPARTITION sp2 VALUES LESS THAN ('2022-01-01')
,SUBPARTITION sp3 VALUES LESS THAN ('2023-01-01')
),
PARTITION p1 VALUES LESS THAN (200)
(SUBPARTITION sp4 VALUES LESS THAN ('2020-01-01')
,SUBPARTITION sp5 VALUES LESS THAN ('2021-01-01')
,SUBPARTITION sp6 VALUES LESS THAN ('2022-01-01')
,SUBPARTITION sp7 VALUES LESS THAN ('2023-01-01'))
);
Query OK, 0 rows affected
Add partitions
The syntax of the ALTER TABLE ... ADD PARTITION statement is as follows:
ALTER TABLE table_name ADD PARTITION (partition_definition)
The following example shows how to add a partition to a partitioned table:
obclient> CREATE TABLE t_log_part_by_range (
log_id bigint NOT NULL
, log_value varchar(50)
, log_date timestamp NOT NULL
) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
(
PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
, PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
, PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
, PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
, PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
);
Query OK, 0 rows affected
obclient> ALTER TABLE t_log_part_by_range ADD PARTITION
(PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
);
Drop partitions
The syntax of the ALTER TABLE ... DROP PARTITION statement is as follows:
ALTER TABLE table_name DROP PARTITION partition_name;
The following example shows how to drop a partition from a partitioned table:
obclient> ALTER TABLE t_log_part_by_range DROP PARTITION M202006;
Query OK, 0 rows affected
Truncate partitions
The syntax of the ALTER TABLE ... TRUNCATE PARTITION statement is as follows:
ALTER TABLE table_name TRUNCATE PARTITION partition_name;
The following example shows how to truncate a partition in a partitioned table:
obclient> CREATE TABLE t_log_part_by_range (
log_id bigint NOT NULL
, log_value varchar(50)
, log_date timestamp NOT NULL
) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
(
PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
, PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
, PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
, PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
, PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
);
Query OK, 0 rows affected
obclient> ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002;
Query OK, 0 rows affected