This topic describes the scenarios where list default hash partitioning is used.
List partitioning is not suitable for all scenarios. In some cases, partition key values cannot be enumerated, or the total number of partition key values is very large but corresponds to a small volume of data. For example, 20% of the partition key values hold 80% of the data, while 80% of the partition key values hold the remaining 20% data. In this case, you can select list default hash partitioning. This allows you to use list partitioning for 80% of your data and hash partitioning for the remaining 20% of your data.
For example, in a multi-tenant business system, the amount of user data generated by each tenant varies considerably. You can use list partitioning to partition tenants that have large data volume and use hash partitioning to partition tenants that have small data volume. The following table lists the data volumes and partitions of different tenants:
Tenant ID | Data volume | Partition |
Key account 1 | 30 million | p1 |
Key account 2 | 26 million | p2 |
Key account 3 | 24 million | p3 |
Key account 4 | 20 million | p4 |
Small and medium-sized customers | 30 million | p_others |
CREATE TABLE cust_orders
(
customer_id VARCHAR(36),
year VARCHAR(60),
order_id INT,
order_content text
) PARTITION BY LIST COLUMNS(customer_id)
(
PARTITION p1 VALUES IN ('Key account 1'),
PARTITION p2 VALUES IN ('Key account 2'),
PARTITION p3 VALUES IN ('Key account 3'),
PARTITION p4 VALUES IN ('Key account 4'),
PARTITION p_others DEFAULT PARTITIONS 3
);