LIST DEFAULT HASH - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to create and modify a list default hash partitioned table.

Background information

PolarDB supports two partition types at the same level: list and hash. Data is first inserted into list partitions. Data that does not comply with the list partitioning rules is placed in the default partition. If the default partition has multiple partitions, hash rules are used. List default hash partitioned tables are commonly used in scenarios where LIST VALUES are unevenly distributed and cannot be fully enumerated. The following figure shows the operations.

Limits

The cluster version meets one of the following requirements. For information about how to view the version of your cluster, see Query an engine version.
- The cluster is of PolarDB for MySQL 8.0.1 and the revision version is 8.0.1.1.34 or later.
- The cluster is of PolarDB for MySQL 8.0.2 and the revision version is 8.0.2.2.1 or later.
One or more default partitions can be created.
List and default subpartitions can be created in combination. However, each partition supports only one default subpartition.
Subpartitions can be of any types if only one default partition is created.
If multiple default partitions exist, only hash or key subpartitions are supported.

Create a list default hash partitioned table

Syntax

PolarDB supports list partitions plus one or more default partitions. The default partition stores data which is not in list partitions. If the default partition is too large, it can be divided into multiple default partitions based on hash rules. The following statement is used to create one or more list default hash partitioned tables:

CREATE TABLE [ schema. ]table_name
 table_definition
   PARTITION BY LIST [COLUMNS] (expr)
   SUBPARTITION BY ...
   (list_partition_definition[, ..., list_partition_definition],
    default_partition_definition
   )

default_partition_definition is:

PARTITION partition_name DEFAULT [PARTITIONS number]

The definition of each partition can also include subpartitions. List default is also supported in subpartitions which is defined in the following way:

SUBPARTITION subpartition_name DEFAULT

Parameters

Parameter	Description
table_name	The name of the table.
partition_name	The name of the partition if only one default partition is created. The name must be different from those in other partitioned tables. The prefix of the partition name if multiple default partitions are created. In this case, the name is in the format of partition_name+sequence number.
subpartition_name	The name of the subpartition. The name must be unique within the table. A maximum of one DEFAULT subpartition is supported.
number	The number of default partitions if the default partition is divided into multiple default partitions based on hash rules. The number parameter is optional. If you do not specify this parameter, a default partition is created.

Examples

Create a default partition.

CREATE TABLE list_default (
  a INT,
  b INT
)
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (1,2,3,4,5),
 PARTITION p1 VALUES IN (6,7,8,9,10),
 PARTITION pd DEFAULT);

Create multiple default partitions.

CREATE TABLE list_default_hash (
  a INT,
  b INT
)
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (1,2,3,4,5),
 PARTITION p1 VALUES IN (6,7,8,9,10),
 PARTITION pd DEFAULT PARTITIONS 3);

Create multiple default partitions which contain VARCHAR data types and uses the list columns partitioning rule.

CREATE TABLE t_goods
(
  country   VARCHAR(30),
  year      VARCHAR(60),
  goods     TEXT
) PARTITION BY LIST COLUMNS(country)
(
  PARTITION p1 VALUES IN ('China'),
  PARTITION p2 VALUES IN ('USA'),
  PARTITION p3 VALUES IN ('Asia'),
  PARTITION p3 VALUES IN ('Singapore'),
  PARTITION p_deft DEFAULT PARTITIONS 5
);

View partitions by executing the EXPLAIN statement.

EXPLAIN SELECT * FROM list_default_hash;

Sample result:

+----+-------------+-------------------+-------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table             | partitions        | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------------+-------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | list_default_hash | p0,p1,pd0,pd1,pd2 | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------------------+-------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.04 sec)

Create a list default hash partitioned table which supports list default subpartitions:

CREATE TABLE test (a int, b int)
PARTITION BY RANGE(a)
SUBPARTITION BY LIST(b) (
PARTITION part0 VALUES LESS THAN (10)
( SUBPARTITION sub0 VALUES IN (1,2,3,4,5),
  SUBPARTITION sub1 DEFAULT),
PARTITION part1 VALUES LESS THAN (20)
( SUBPARTITION sub2 VALUES IN (1,2,3,4,5),
  SUBPARTITION sub3 DEFAULT),
PARTITION part2 VALUES LESS THAN (30)
( SUBPARTITION sub4 VALUES IN (1,2,3,4,5),
  SUBPARTITION sub5 DEFAULT));

Create a list default hash partitioned table which supports only hash or key subpartitions when multiple list default hash partitions are contained:

CREATE TABLE list_default_hash_sub (
  a INT,
  b INT
)
PARTITION BY LIST (a)
SUBPARTITION BY HASH (b) SUBPARTITIONS 20
(PARTITION p0 VALUES IN (1,2,3,4,5),
 PARTITION p1 VALUES IN (6,7,8,9,10),
 PARTITION pd DEFAULT PARTITIONS 3);

Modify a list default hash partitioned table

List default hash partitions support only the following statements: ALTER TABLE ADD PARTITION, ALTER TABLE DROP PARTITION, ALTER TABLE REORGANIZE PARTITION, ALTER TABLE TRUNCATE PARTITION, ALTER TABLE EXCHANGE PARTITION, ALTER TABLE OPTIMIZE PARTITION, ALTER TABLE REBUILD PARTITION, ALTER TABLE REPAIR PARTITION, ALTER TABLE ANALYZE PARTITION, and ALTER TABLE CHECK PARTITION.

This topic describes how to modify a list default hash partitioned table by executing the ALTER TABLE ADD PARTITION, ALTER TABLE DROP PARTITION, and ALTER TABLE REORGANIZE PARTITION statements. For more information about other statements, see Modify a partitioned table.

ALTER TABLE ADD PARTITION

ADD DEFAULT PARTITION

Syntax

If the partitioned table contains only list partitions, add the default partition by executing the ADD PARTITION statement to switch it to a list default hash partitioned table.

ALTER TABLE table_name ADD PARTITION(default_partition_definition)

Examples

Add a default partition:

CREATE TABLE list_tab (
  a INT,
  b INT
)
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (1,2,3,4,5),
 PARTITION p1 VALUES IN (6,7,8,9,10)
);
ALTER TABLE list_tab ADD PARTITION(PARTITION pd DEFAULT);

Add two default partitions:

CREATE TABLE list_tab (
  a INT,
  b INT
)
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (1,2,3,4,5),
 PARTITION p1 VALUES IN (6,7,8,9,10)
);
ALTER TABLE list_tab ADD PARTITION(PARTITION pd DEFAULT PARTITIONS 2);

ADD LIST PARTITION
For a list default hash partitioned table in PolarDB for MySQL 8.0.2.2.11 and later, you can add WITHOUT VALIDATION to the ALTER TABLE ADD PARTITION statement to add list partitions.
You need to ensure that the list values of the new partition are new data, which means that no data in the default partition meets the new list partitioning rule. Otherwise, execute the ALTER TABLE REORGANIZE PARTITION statement to separate part of the data from the default partition to create a new list partition.
Syntax
```
ALTER TABLE table_name ADD PARTITION(
  list_partition_definition[, ..., list_partition_definition])
WITHOUT VALIDATION
```
Examples
Adds a list partition.
```
CREATE TABLE list_default_hash (
  a INT,
  b INT
)
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (1,2,3,4,5),
 PARTITION p1 VALUES IN (6,7,8,9,10),
 PARTITION pd DEFAULT PARTITIONS 3);

ALTER TABLE list_default_hash ADD PARTITION(
  PARTITION p2 VALUES IN (11,12,13)
)WITHOUT VALIDATION;
```
After the preceding statement is executed, a list partition named p2 is added to the list_default_hash table. p2 contains no data.
Note
You need to ensure that the value of the a parameter in the default partition is not 11, 12, or 13. Otherwise, you may fail to find these data in the default partition after the list partition is added.

ALTER TABLE DROP PARTITION

For more information about the DROP PARTITION statement, see DROP PARTITION.

Examples

Note that the DROP PARTITION statement deletes all default partitions at a time. You cannot execute this statement to delete only some default partitions.

Execute the DROP PARTITION statement to delete all partitions.

ALTER TABLE list_default_hash DROP PARTITION pd0,pd1,pd2;
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

Error message

An error is returned when you delete only some default partitions.

ALTER TABLE list_default_hash DROP PARTITION pd0;

The following error message is returned:

ERROR 8078 (HY000): DROP PARTITION cannot be used on default partitions of LIST DEFAULT, except once dropping all default partitions

ALTER TABLE REORGANIZE PARTITION

For more information about the REORGANIZE PARTITION statement, see REORGANIZE PARTITION.

Examples

Note that the REORGANIZE PARTITION statement modifies all default partitions at a time. You cannot execute this statement to modify only some default partitions.

You can execute the REORGANIZE PARTITION statement to modify the number of default partitions:
```
ALTER TABLE list_default_hash
REORGANIZE PARTITION
  pd0,pd1
INTO(
  PARTITION pd DEFAULT PARTITIONS 3);
```
After the preceding statement is executed, the number of default partitions is changed from two to three.
You can execute the REORGANIZE PARTITION statement to split a list partition from a default partition:
```
ALTER TABLE list_default_hash
REORGANIZE PARTITION
  pd0,pd1
INTO (
 PARTITION p2 VALUES IN (20,21),
 PARTITION pd DEFAULT PARTITIONS 2);
```
After the preceding statement is executed, a list partition named p2 is added to the list_default_hash table. p2 contains the data which is separated from the default partition that matches the VALUES IN (20,21) rule.
You can execute the REORGANIZE PARTITION statement to merge a list partition into a default partition:
```
ALTER TABLE list_default_hash
REORGANIZE PARTITION
  p2, pd0, pd1
INTO (
 PARTITION pd DEFAULT PARTITIONS 2);
```
After the preceding statement is executed, the list partition p2 is merged into the default partition.
You can execute the REORGANIZE PARTITION to split some values from a default partition and add them to a list partition:
```
ALTER TABLE list_default
REORGANIZE partition
  p2, pd0, pd1
INTO (
  PARTITION p2 VALUES IN (20,21,22,23,24),
  PARTITION pd DEFAULT PARTITIONS 4);
```
After the preceding statement is executed, the definition of p2 changes from PARTITION p2 VALUES IN (20,21) to PARTITION p2 VALUES IN (20,21,22,23,24). The data that matches the VALUES IN (20,21,22,23,24) rule is moved from the default partition to p2.

References

When to use list default hash partitioning