Create a HASH-RANGE partitioned table - PolarDB

HASH-RANGE partitioning divides table data into hash partitions, each further split into range subpartitions. Use this strategy when you need to distribute rows evenly across partitions (via hashing) while still being able to prune by a range condition on a second column.

Syntax

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

partition_definition:

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

subpartition_definition:

SUBPARTITION subpartition_name
  VALUES LESS THAN {value | MAXVALUE}

Parameters

Parameter	Description
`table_name`	The name of the table.
`expr`	The partition expression. Must be of the INT type. The string type is not supported.
`value`	The boundary value of the partition.
`MAXVALUE`	The maximum value of the partition.
`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.

Usage notes

partition_name values must be unique within the table.
subpartition_name values must be unique within the table.

Example

The following statement creates a sales_hash_range table with three hash partitions (p0, p1, p2), each containing four range subpartitions based on part_no values:

CREATE TABLE sales_hash_range
(
  dept_no  INT,
  part_no  INT,
  country  VARCHAR(20),
  date     DATE,
  amount   INT
)
PARTITION BY HASH(dept_no)
SUBPARTITION BY RANGE(part_no)
(
  PARTITION p0 (
    SUBPARTITION s0 VALUES LESS THAN (4),
    SUBPARTITION s1 VALUES LESS THAN (7),
    SUBPARTITION s2 VALUES LESS THAN (10),
    SUBPARTITION s3 VALUES LESS THAN (13)
  ),
  PARTITION p1 (
    SUBPARTITION s4 VALUES LESS THAN (4),
    SUBPARTITION s5 VALUES LESS THAN (7),
    SUBPARTITION s6 VALUES LESS THAN (10),
    SUBPARTITION s7 VALUES LESS THAN (13)
  ),
  PARTITION p2 (
    SUBPARTITION s8 VALUES LESS THAN (4),
    SUBPARTITION s9 VALUES LESS THAN (7),
    SUBPARTITION s10 VALUES LESS THAN (10),
    SUBPARTITION s11 VALUES LESS THAN (13)
  )
);

dept_no determines which hash partition (p0–p2) a row belongs to. Within each partition, part_no determines the subpartition: values less than 4 go to the first subpartition, values less than 7 to the second, and so on.