Create a RANGE-HASH Partitioned Table for Scalable Data Distribution - PolarDB

RANGE-HASH partitioning combines the manageability of RANGE partitioning with the even data distribution of HASH partitioning. The first-level RANGE or RANGE COLUMNS partition organizes data into value-based ranges — suited for time-series data, billing cycles, or archiving by period. The second-level HASH or KEY subpartition spreads rows evenly within each range, preventing hotspots within a single range partition.

Use RANGE-HASH when your workload has both of these characteristics:

Queries filter or prune by a range dimension (such as date or transaction amount).
Data within each range is not distributed evenly across nodes, causing hotspots.

Syntax

CREATE TABLE [schema.]table_name
  table_definition
  PARTITION BY RANGE {(expr) | COLUMNS(column_list)}
  SUBPARTITION BY {[LINEAR] HASH(expr) [SUBPARTITIONS number]
                  | [LINEAR] KEY [ALGORITHM={1|2}] (column_list)}
  (partition_definition [, partition_definition] ...);

Where partition_definition is:

PARTITION partition_name
  VALUES LESS THAN {(expr | value_list) | MAXVALUE}
  (subpartition_definition [, subpartition_definition] ...)

And subpartition_definition is:

SUBPARTITION subpartition_name

Parameters

Parameter	Description
`expr`	Partition expression. Supports INT type only. String types are not supported. To partition by a date or string column, use `RANGE COLUMNS(column_list)` instead.
`column_list`	List of partitioning columns. Expressions are not supported.
`number`	Number of subpartitions.
`value`	Boundary value of the partition.
`value_list`	List of boundary values. Used with `RANGE COLUMNS()`.
`MAXVALUE`	Represents the maximum possible value, catching all rows beyond the last explicit boundary.
`partition_name`	Name of the partition. Must be unique within the same table.
`subpartition_name`	Name of the subpartition. Must be unique within the same table.

Examples

Partition by an integer expression

Use RANGE (expr) when the partition key is an integer column or integer expression.

CREATE TABLE sales_range_hash_1
(
  s_id        varchar(20),
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT,
  primary key (s_id, amount)
)
PARTITION BY RANGE (amount)
SUBPARTITION BY KEY(s_id) SUBPARTITIONS 3
(
  PARTITION p1 VALUES LESS THAN (1000),
  PARTITION p2 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (3000),
  PARTITION p4 VALUES LESS THAN (4000)
);

This creates 4 × 3 = 12 physical partitions: 4 RANGE partitions by amount, each split into 3 KEY subpartitions by s_id.

Partition by column list

Use RANGE COLUMNS(column_list) when the partition key is a date, string, or multi-column combination. Unlike RANGE (expr), RANGE COLUMNS accepts non-integer columns directly.

CREATE TABLE sales_range_hash_2
(
  s_id        varchar(20),
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT,
  primary key (s_id, date)
)
PARTITION BY RANGE COLUMNS(date)
SUBPARTITION BY KEY(s_id) SUBPARTITIONS 3
(
  PARTITION p1 VALUES LESS THAN('2023-01-01'),
  PARTITION p2 VALUES LESS THAN('2023-02-01'),
  PARTITION p3 VALUES LESS THAN('2023-03-01'),
  PARTITION p4 VALUES LESS THAN('2023-04-01')
);

This creates 4 × 3 = 12 physical partitions: 4 RANGE COLUMNS partitions by date, each split into 3 KEY subpartitions by s_id.

Usage notes

`expr` supports INT only. When using RANGE (expr), the partition expression must evaluate to an integer. To partition by a date or string column, use RANGE COLUMNS(column_list) instead.
Subpartition names must be unique within the same table. Reusing a subpartition name across partitions is not allowed.