Partitioning operations include repartitioning, adding partitions, dropping partitions, and truncating partitions.
Repartition
The syntax for repartitioning is as follows:
ALTER TABLE table_name PARTITION BY (partition_definitions);
Example of repartitioning
Example of changing a hash partition to a key partition
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 of modifying a first-level hash partitioned table into a template-based hash+range subpartitioned 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: Convert a HASH+RANGE composite 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 for adding a partition is as follows:
ALTER TABLE table_name ADD PARTITION (partition_definition)
The following example shows how to add a partition:
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 a partition
The syntax for dropping a partition is as follows:
ALTER TABLE table_name DROP PARTITION partition_name;
The following example shows how to delete a partition:
obclient> ALTER TABLE t_log_part_by_range DROP PARTITION M202006;
Query OK, 0 rows affected
Truncate partitions
The syntax for truncating a partition is as follows:
ALTER TABLE table_name TRUNCATE PARTITION partition_name;
The following example shows how to truncate a partition:
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