Create and modify a LIST DEFAULT HASH partitioned table - PolarDB

When list partition values are unevenly distributed or cannot be fully enumerated, a standard LIST partition fails for any row whose value doesn't appear in a VALUES IN clause — the insert returns an error. LIST DEFAULT HASH solves this by adding one or more default partitions that catch all unmatched rows. When multiple default partitions are needed, hash rules distribute those rows across them evenly.

How it works

Each inserted row is evaluated against the list partition rules.
Rows that match a VALUES IN clause go into the corresponding list partition.
Rows that don't match any list rule fall into the default partition.
If multiple default partitions exist, hash rules distribute the unmatched rows across them.

Version requirements

LIST DEFAULT HASH requires one of the following cluster versions. To check your version, see Query an engine version.

PolarDB for MySQL 8.0.1, revision version 8.0.1.1.34 or later
PolarDB for MySQL 8.0.2, revision version 8.0.2.2.1 or later

Limitations

Limitation	Details
Default partitions	One or more default partitions can be created.
Subpartition combination	List and default subpartitions can be combined, but each partition supports at most one default subpartition.
Subpartition types (single default)	If only one default partition exists, subpartitions can be of any type.
Subpartition types (multiple defaults)	If multiple default partitions exist, only hash or key subpartitions are supported.

Create a list default hash partitioned table

Syntax

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

Where default_partition_definition is:

PARTITION partition_name DEFAULT [PARTITIONS number]

To add a default subpartition within a partition:

SUBPARTITION subpartition_name DEFAULT

Parameters

Parameter	Description
`table_name`	Name of the table.
`partition_name`	For a single default partition: the partition name, which must differ from all other partition names in the table. For multiple default partitions: the name prefix; actual partitions are named `partition_name0`, `partition_name1`, and so on.
`subpartition_name`	Name of the subpartition. Must be unique within the table. Each partition supports at most one DEFAULT subpartition.
`number`	Number of default partitions when hash rules are used. Optional — if omitted, a single default partition is created.

Examples

Create a single default partition

All rows with values outside 1–10 go into the pd 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

Unmatched rows are distributed across three default partitions (pd0, pd1, pd2) using hash rules.

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

Run EXPLAIN to confirm the partition layout:

EXPLAIN SELECT * FROM list_default_hash;

Expected output:

+----+-------------+-------------------+-------------------+------+---------------+------+---------+------+------+----------+-------+
| 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 multiple default partitions with VARCHAR columns

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

Create a table with default subpartitions

When only one default partition exists, subpartitions can be of any type. The example below uses list subpartitions with one default subpartition per partition.

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 table with hash subpartitions across multiple default partitions

When multiple default partitions exist, 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 partitioned tables support the following ALTER TABLE statements: ADD PARTITION, DROP PARTITION, REORGANIZE PARTITION, TRUNCATE PARTITION, EXCHANGE PARTITION, OPTIMIZE PARTITION, REBUILD PARTITION, REPAIR PARTITION, ANALYZE PARTITION, and CHECK PARTITION.

The sections below cover ADD PARTITION, DROP PARTITION, and REORGANIZE PARTITION. For the remaining statements, see Modify a partitioned table.

Add a partition

Add a default partition

If a table currently has only list partitions, add a default partition to convert it to a list default hash partitioned table.

ALTER TABLE table_name ADD PARTITION (default_partition_definition)

Add one 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 a list partition

Available in PolarDB for MySQL 8.0.2.2.11 and later.

Adding a new list partition normally triggers a full scan of the default partition to verify that no existing rows belong to the new partition. When the default partition contains a large amount of data, this scan can be slow. To skip it, append WITHOUT VALIDATION to the statement — but only when you are certain that no data in the default partition matches the new list values.

If you cannot confirm that, use ALTER TABLE REORGANIZE PARTITION instead to split matching data out of the default partition before adding the new list partition.

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

Example

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 statement runs, partition p2 is added to list_default_hash and contains no data.

Important

When using WITHOUT VALIDATION, confirm that the default partition contains no rows where a is 11, 12, or 13. If it does, those rows become unreachable after the new list partition is added.

Drop a partition

For full reference on DROP PARTITION, see DROP PARTITION.

DROP PARTITION removes all default partitions at once. Dropping only a subset of the default partitions is not supported.

Drop all default 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: dropping a subset of default partitions

Attempting to drop only some default partitions returns an error:

ALTER TABLE list_default_hash DROP PARTITION pd0;

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

Reorganize partitions

For full reference on REORGANIZE PARTITION, see REORGANIZE PARTITION.

REORGANIZE PARTITION acts on all default partitions at once. Modifying only a subset of the default partitions is not supported.

Change the number of default partitions

The following statement increases the number of default partitions from two to three:

ALTER TABLE list_default_hash
REORGANIZE PARTITION pd0, pd1
INTO (
  PARTITION pd DEFAULT PARTITIONS 3);

Split a list partition out of the default partition

The following statement creates a new list partition p2 containing rows that match VALUES IN (20,21), taken from the default partition:

ALTER TABLE list_default_hash
REORGANIZE PARTITION pd0, pd1
INTO (
  PARTITION p2 VALUES IN (20,21),
  PARTITION pd DEFAULT PARTITIONS 2);

Merge a list partition into the default partition

The following statement moves all rows from list partition p2 back into the default partition:

ALTER TABLE list_default_hash
REORGANIZE PARTITION p2, pd0, pd1
INTO (
  PARTITION pd DEFAULT PARTITIONS 2);

Expand a list partition's value set

The following statement extends p2 from VALUES IN (20,21) to VALUES IN (20,21,22,23,24). Rows in the default partition that match the new values are moved to p2:

ALTER TABLE list_default
REORGANIZE PARTITION p2, pd0, pd1
INTO (
  PARTITION p2 VALUES IN (20,21,22,23,24),
  PARTITION pd DEFAULT PARTITIONS 4);

What's next

When to use list default hash partitioning