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.
Parameter |
Level |
Description |
---|---|---|
rds_list_default_partition_enabled |
Global |
Enables or disables LIST DEFAULT HASH. Value:
|
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.
- ADD DEFAULT 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.
- Execute the REORGANIZE PARTITION statement to change the number of DEFAULT partitions:
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot