Create a HASH-LIST partitioned table

HASH-LIST partitioning distributes rows across partitions by hashing a partition expression, then places each row into a subpartition based on a list of discrete values. Use this partitioning strategy when you need even data distribution at the top level (hash) and categorical grouping within each partition (list).

Syntax

CREATE TABLE [schema.]table_name
  table_definition
  PARTITION BY [LINEAR] HASH(expr)
    SUBPARTITION BY LIST (expr)
    (partition_definition [, partition_definition] ...)

partition_definition is:

PARTITION partition_name
  (subpartition_definition [, subpartition_definition] ...)

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 of the INT type — string types are not supported.
`value_list`	The list of boundary values for the 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.

Both PARTITION BY HASH and SUBPARTITION BY LIST expressions must evaluate to an INT. String types are not supported for either expression.

The following example creates a sales_hash_list table partitioned by dept_no (hash) and subpartitioned by part_no (list). The table has three partitions, each containing three subpartitions that cover values 1–6.

CREATE TABLE sales_hash_list
(
  dept_no     INT,
  part_no     INT,
  country     VARCHAR(20),
  date        DATE,
  amount      INT
)
PARTITION BY HASH(dept_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)
  )
);

How rows are routed

PolarDB determines the target subpartition for each row in two steps:

Hash step — dept_no is hashed to select one of the three partitions (dp0, dp1, or dp2).
List step — part_no is matched against the VALUES IN lists within that partition to select the subpartition.

The following examples illustrate how specific rows are routed. The partition assigned by the hash step depends on the hash function and the number of partitions.

`dept_no`	`part_no`	Example partition	Subpartition	Reason
5	3	dp1	p4	`dept_no=5` hashes to `dp1`; `part_no=3` matches `VALUES IN (3, 4)`
2	6	dp0	p2	`dept_no=2` hashes to `dp0`; `part_no=6` matches `VALUES IN (5, 6)`
9	1	dp2	p6	`dept_no=9` hashes to `dp2`; `part_no=1` matches `VALUES IN (1, 2)`