KEY-LIST partitioning is a form of composite partitioning (also called subpartitioning) that combines KEY partitioning at the top level with LIST subpartitioning. Each row is first assigned to a partition by hashing the key expression, then placed into a subpartition based on a discrete list of values. Use KEY-LIST when you want hash-based distribution across partitions to spread write load evenly, and value-based filtering within each partition to support efficient range-like queries on a categorical column.
The partition expression (expr) must be of INT type. String types are not supported.Syntax
CREATE TABLE [schema.]table_name
table_definition
PARTITION BY [LINEAR] KEY(expr)
SUBPARTITION BY LIST (expr)
(partition_definition [, partition_definition] ...);Where partition_definition is:
PARTITION partition_name
(subpartition_definition [, subpartition_definition] ...)And subpartition_definition is:
SUBPARTITION subpartition_name
VALUES IN (value_list)Parameters
| Parameter | Description |
|---|---|
table_name | The name of the table. |
expr | The partition expression. Must be INT type; string types are not supported. |
value_list | The list of values assigned to a subpartition. |
partition_name | The name of the partition. Must be unique within the table. |
subpartition_name | The name of the subpartition. Must be unique within the table. |
Examples
The following creates a sales table with three partitions, each containing three subpartitions based on part number values:
CREATE TABLE sales_key_list (
dept_no VARCHAR(20),
part_no INT,
country VARCHAR(20),
date DATE,
amount INT
)
PARTITION BY KEY(part_no)
SUBPARTITION BY LIST(part_no)
(
PARTITION dp0 (
SUBPARTITION p0 VALUES IN (1, 2),
SUBPARTITION p1 VALUES IN (3, 4),
SUBPARTITION p2 VALUES IN (5, 6)
),
PARTITION dp1 (
SUBPARTITION p3 VALUES IN (1, 2),
SUBPARTITION p4 VALUES IN (3, 4),
SUBPARTITION p5 VALUES IN (5, 6)
),
PARTITION dp2 (
SUBPARTITION p6 VALUES IN (1, 2),
SUBPARTITION p7 VALUES IN (3, 4),
SUBPARTITION p8 VALUES IN (5, 6)
)
);