LIST DEFAULT HASH

LIST DEFAULT HASH 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 usually used in scenarios where LIST VALUES are unevenly distributed and cannot be fully enumerated.

Prerequisites

The kernel version of GaussDB(for MySQL) must be 2.0.54.240600 or later.
rds_list_default_partition_enabled has been set to ON.

Constraints

You can create one or more DEFAULT partitions.
You can create LIST and DEFAULT subpartitions together, but each partition can only have one DEFAULT subpartition.
If there is only one DEFAULT partition, subpartitions can be of any types.
If there are multiple DEFAULT partitions, only HASH or KEY subpartitions are supported.

Parameters

On the Parameters page, set rds_list_default_partition_enabled to enable or disable LIST DEFAULT HASH.

**Table 1** Parameter description
Parameter	Level	Description
rds_list_default_partition_enabled	Global	Enables or disables LIST DEFAULT HASH. Value: ON: LIST DEFAULT HASH is enabled. OFF: LIST DEFAULT HASH is disabled.

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

default_partition_definition is:

PARTITION partition_name DEFAULT [PARTITIONS number]

The definition of each partition can also contain subpartitions. Subpartitions can also use LIST DEFAULT. The definition is as follows:

SUBPARTITION subpartition_name DEFAULT

**Table 2** Parameter description
Parameter	Description
table_name	The name of the table to be created.
partition_name	The name of the partition if there is only one DEFAULT partition. The name must be different from those in other partitioned tables. The prefix of the partition name if there are multiple DEFAULT partitions. The partition name is in the format of partition_name+sequence number.
subpartition_name	The name of the subpartition. The name must be unique within a table. Only 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. This parameter is optional. If you do not specify it, a DEFAULT partition is created.

Examples

Create a single DEFAULT partition:

CREATE TABLE list_default_tbl (
  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);

Use LIST 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 ('India'),
  PARTITION p_deft DEFAULT PARTITIONS 5
);

Execute the EXPLAIN statement to view partitions:

EXPLAIN SELECT * FROM list_default_hash;

The following information is displayed:

+----+-------------+-------------------+-------------------+------+---------------+------+---------+------+------+----------+-------+
| 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 there are multiple LIST DEFAULT HASH partitions:

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

Modifying a LIST DEFAULT HASH Partitioned Table

LIST DEFAULT HASH partitions support all the statements for modifying partitioned tables, including 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.

ALTER TABLE ADD PARTITION
- ADD DEFAULT PARTITION
  If a partitioned table contains only LIST partitions, run ADD PARTITION to add a DEFAULT partition so that the table becomes a LIST DEFAULT HASH partitioned table.
```
ALTER TABLE table_name ADD PARTITION(default_partition_definition)
```
  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
  You can add WITHOUT VALIDATION to the ALTER TABLE ADD PARTITION statement to add LIST partitions.
```
ALTER TABLE table_name ADD PARTITION(
  list_partition_definition[, ..., list_partition_definition])
WITHOUT VALIDATION
```
  Add 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 statement is executed, a LIST partition named p2 is added to table list_default_hash. There is no data in p2.
  
  If you use WITHOUT VALIDATION to add a LIST partition, you need to manually execute ALTER TABLE ... REBUILD ALL to reallocate data. Otherwise, data will not be reallocated. Data that meets the new partition definition will be still stored in the DEFAULT partition. During query, all DEFAULT partitions will be marked and not pruned. As a result, the query performance deteriorates. You are advised to use the ALTER TABLE REORGANIZE PARTITION statement to separate some data from the DEFAULT partition and create a new LIST partition.
ALTER TABLE DROP PARTITION
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
```
An error is reported when you delete only some DEFAULT partitions.
```
ALTER TABLE list_default_hash DROP PARTITION pd0;
```
The error message is as follows:

ERROR 8078 (HY000): DROP PARTITION cannot be used on default partitions of LIST DEFAULT, except once dropping all default partitions
ALTER TABLE REORGANIZE PARTITION
The REORGANIZE PARTITION statement modifies all DEFAULT partitions at a time. You cannot execute this statement to modify only some DEFAULT partitions.
- Execute the REORGANIZE PARTITION statement to change the number of DEFAULT partitions:
```
ALTER TABLE list_default_hash
REORGANIZE PARTITION
  pd0,pd1
INTO(
  PARTITION pd DEFAULT PARTITIONS 3);
```
  After the statement is executed, the number of DEFAULT partitions changes from 2 to 3.
- 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 statement is executed, a LIST partition named p2 is added to the list_default_hash partitioned table. p2 contains data that meets the VALUES IN (20,21) rule and is separated from the DEFAULT partition.
- 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 statement is executed, the LIST partition p2 is merged into the DEFAULT partition.
- Execute the REORGANIZE PARTITION statement 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 statement is executed, the definition of p2 is changed from PARTITION p2 VALUES IN (20,21) to PARTITION p2 VALUES IN (20,21,22,23,24). Any data that matches the VALUES IN (20,21,22,23,24) rule is then transferred from the DEFAULT partition to p2.