How to create a LIST DEFAULT HASH hybrid partitioned table - PolarDB

This topic describes how to create and modify a LIST DEFAULT HASH partitioned table.

Background information

PolarDB supports a hybrid partitioning strategy that combines LIST and HASH partitioning at the same level. In this model, data is first evaluated against a set of LIST partitions. Any data that does not match a defined LIST value is placed in a default partition. If this default partition is subdivided, a HASH function distributes the data across the subdivisions. This approach is ideal for use cases where LIST values are unevenly distributed or when enumerating all possible values is impractical. LIST DEFAULT HASH

Limitations

Your cluster must meet one of the following version requirements. To check your cluster version, see View the version of a cluster.
- PolarDB for MySQL 8.0.1 with revision version 8.0.1.1.34 or later.
- PolarDB for MySQL 8.0.2 with revision version 8.0.2.2.1 or later.
One or more default partitions are supported at the partition level.
You can use a combination of LIST and DEFAULT for subpartitions, but each partition supports only one default subpartition.
If a partition has only one default partition, all types of subpartitions are supported.
If a partition has multiple default partitions, only HASH or KEY subpartitions are supported.

Create a LIST DEFAULT HASH partitioned table

Syntax

PolarDB lets you create partitioned tables that store data not matching a specified LIST partition in a default partition. If this partition grows too large, you can use the HASH method to divide it into multiple partitions.

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

The default_partition_definition is as follows:

PARTITION partition_name DEFAULT [PARTITIONS number]

Each partition definition can also include subpartitions. LIST DEFAULT is also supported for subpartitions and is defined as follows:

SUBPARTITION subpartition_name DEFAULT

Parameter Description

Parameter	Description
table_name	The name of the table to create.
partition_name	If there is only one default partition, this parameter specifies the name of the partition. The name must be unique within the table. If there are multiple default partitions, this parameter specifies the name prefix for the partitions. The final partition name is formed by appending a sequence number to this prefix.
subpartition_name	The name of the subpartition. The name must be unique within the table. Each partition can have at most one default subpartition.
number	Specifies the number of partitions to create from the default partition by using a HASH method. The `PARTITIONS number` clause is optional. If this clause is omitted, a single default partition is created.

Example

The following example shows how to create a single 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);

The following example shows how to 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);

The following example shows how to use LIST COLUMNS when the partition key has a VARCHAR data type:

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 p4 VALUES IN ('Singapore'),
  PARTITION p_deft DEFAULT PARTITIONS 5
);

Use the EXPLAIN statement to view the partitions:

EXPLAIN SELECT * FROM list_default_hash;

The output is as follows:

+----+-------------+-------------------+-------------------+------+---------------+------+---------+------+------+----------+-------+
| 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)

The following example shows how to use LIST DEFAULT for 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));

If a partition has multiple default partitions, only HASH or KEY subpartitions are supported:

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 operations such as 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 use ALTER TABLE ADD PARTITION, ALTER TABLE DROP PARTITION, and ALTER TABLE REORGANIZE PARTITION. For information about other operations for modifying LIST DEFAULT HASH partitioned tables, see Modify a partitioned table.

ALTER TABLE ADD PARTITION

ADD DEFAULT PARTITION

Syntax

For a standard LIST partitioned table without a default partition, you can use the ADD PARTITION statement to add a default partition. This converts the table into a LIST DEFAULT HASH partitioned table.

ALTER TABLE table_name ADD PARTITION(default_partition_definition)

Example

The following example shows how to add a single 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);

The following example shows how to 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 PolarDB for MySQL 8.0.2.2.11 and later, you can use the WITHOUT VALIDATION option with the ALTER TABLE ADD PARTITION statement to add a LIST partition to a LIST DEFAULT HASH partitioned table.

You must ensure that the values for the new LIST partition do not already exist in the default partition. If they do, use the ALTER TABLE REORGANIZE PARTITION statement to move the data from the default partition into a new LIST partition.

Syntax
```
ALTER TABLE table_name ADD PARTITION(
  list_partition_definition[, ..., list_partition_definition])
WITHOUT VALIDATION
```
Example

Add a new 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 execution, a LIST partition p2 is added to the list_default_hash table, and p2 contains no data.

Note
Ensure that the default partition does not contain the values 11, 12, and 13 for column a. Otherwise, after the new LIST partition is added, these rows may become inaccessible.

ALTER TABLE DROP PARTITION

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

Example

When you use the DROP PARTITION operation, you must drop all default partitions at once. You cannot drop only a subset of default partitions.

Run the DROP PARTITION command to drop 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 occurs if you try to drop only some of the default partitions.

ALTER TABLE list_default_hash DROP PARTITION pd0;

The command returns the following error message:

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 syntax, see REORGANIZE PARTITION.

Example

When you use the REORGANIZE PARTITION operation, you must modify all default partitions at once. You cannot modify only a subset of default partitions.

Use REORGANIZE PARTITION to change the number of default partitions:
```
ALTER TABLE list_default_hash
REORGANIZE PARTITION
  pd0,pd1
INTO(
  PARTITION pd DEFAULT PARTITIONS 3);
```
After you run this statement, the number of default partitions changes from two to three.
Use REORGANIZE PARTITION to split a new LIST partition from the default partitions:
```
ALTER TABLE list_default_hash
REORGANIZE PARTITION
  pd0,pd1
INTO (
 PARTITION p2 VALUES IN (20,21),
 PARTITION pd DEFAULT PARTITIONS 2);
```
This statement adds a new LIST partition named p2 to the table and moves rows that match VALUES IN (20,21) from the default partitions into p2.
Use REORGANIZE PARTITION to merge a LIST partition into the default partitions:
```
ALTER TABLE list_default_hash
REORGANIZE PARTITION
  p2, pd0, pd1
INTO (
 PARTITION pd DEFAULT PARTITIONS 2);
```
This statement merges the LIST partition p2 into the default partitions.
Use REORGANIZE PARTITION to expand a LIST partition by moving values from the default partitions:
```
ALTER TABLE list_default_hash
REORGANIZE partition
  p2, pd0, pd1
INTO (
  PARTITION p2 VALUES IN (20,21,22,23,24),
  PARTITION pd DEFAULT PARTITIONS 4);
```
This statement changes the definition of p2 from PARTITION p2 VALUES IN (20,21) to PARTITION p2 VALUES IN (20,21,22,23,24). It also moves the corresponding data from the default partitions to p2.

PolarDB:LIST DEFAULT HASH

Background information

Limitations

Create a LIST DEFAULT HASH partitioned table

Modify a LIST DEFAULT HASH partitioned table

ALTER TABLE ADD PARTITION

ALTER TABLE DROP PARTITION

ALTER TABLE REORGANIZE PARTITION

Related topics