Skip to main content

Partitioning operations

The partitioning operations supported by seekdb include repartitioning, adding partitions, dropping partitions, and truncating partitions.

Repartitioning​

The syntax for repartitioning is as follows:

ALTER TABLE table_name PARTITION BY (partition_definitions);

Repartitioning examples​

Example of changing a hash partitioned table to a key partitioned table​

CREATE TABLE tbl2(c1 INT, c2 DATETIME, PRIMARY KEY(c1, c2))
PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected (0.104 sec)

ALTER TABLE tbl2 PARTITION BY KEY(c1) PARTITIONS 10;
Query OK, 0 rows (0.994 sec)

Example of changing a hash partitioned table to a hash+range partitioned table​

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 affeaffected (0.746 sec)

Example of changing a hash+range partitioned table to a range columns+range columns partitioned table​

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 (0.836 sec)

Adding partitions​

The syntax for adding partitions is as follows:

ALTER TABLE table_name ADD PARTITION (partition_definition)

The following example shows how to add partitions:

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 (0.094 sec)

  ALTER TABLE t_log_part_by_range ADD PARTITION
           (PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
           );

Dropping partitions​

The syntax for dropping partitions is as follows:

ALTER TABLE table_name DROP PARTITION partition_name;

The following example shows how to drop partitions:

ALTER TABLE t_log_part_by_range DROP PARTITION M202006;
Query OK, 0 rows affected (0.063 sec)

Truncating partitions​

The syntax for truncating partitions is as follows:

ALTER TABLE table_name TRUNCATE PARTITION partition_name;

The following example shows how to truncate partitions:

 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 (0.078 sec)

  ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002;
  Query OK, 0 rows affected (0.086 sec)

Contents

Edit this page