Create a HASH-KEY partitioned table - PolarDB

HASH-KEY is a composite partitioning strategy that combines a HASH primary partition with a KEY subpartition.

HASH partition: distributes rows based on expr, where expr must be an integer expression.
KEY subpartition: distributes rows within each partition based on a column list.

Syntax

CREATE TABLE [schema.]table_name
  table_definition
  PARTITION BY [LINEAR] HASH(expr) [PARTITIONS num]
  SUBPARTITION BY [LINEAR] KEY(expr) [SUBPARTITIONS sub_num]
  (partition_definition [, partition_definition] ...)

partition_definition is:

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

subpartition_definition is:

SUBPARTITION subpartition_name

Parameters

Parameter	Description
`table_name`	The name of the table.
`expr`	The partition expression for the HASH clause. Must be of the INT type. String types are not supported.
`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.

Example

The following example creates a composite partitioned table with 3 HASH partitions on dept_no and 2 KEY subpartitions on country.

CREATE TABLE sales_hash_key
(
  dept_no     INT,
  part_no     INT,
  country     VARCHAR(20),
  date        DATE,
  amount      INT
)
  PARTITION BY HASH(dept_no) PARTITIONS 3
  SUBPARTITION BY KEY(country) SUBPARTITIONS 2
;