Create a KEY-HASH partitioned table - PolarDB

KEY-HASH partitioning combines KEY primary partitions with HASH subpartitions: rows are first routed to a partition by KEY, then further divided within each partition by HASH.

Syntax

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

partition_definition:

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

subpartition_definition:

SUBPARTITION subpartition_name

Parameters

Parameter	Description
`table_name`	The name of the table.
`expr`	The expression of the partition. It must be of the INT type. The string type is 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

Create a key-hash partitioned table with 3 KEY partitions, each containing 2 HASH subpartitions:

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

The table has 6 subpartitions in total (3 partitions x 2 subpartitions). Rows are distributed first by dept_no (KEY), then further divided within each partition by HASH.