Range-hash partitioning concept and use cases - PolarDB

Range-hash partitioning combines range partitioning at the top level with hash subpartitioning at the second level. It is well-suited for large historical tables that are frequently joined with other large tables—typical of data warehouse workloads—and for hash-partitioned tables that require lifecycle-based data movement.

Benefits

Range-hash partitioning delivers value on two dimensions:

Partition pruning at the range level — queries that filter by the range column (such as a date range) skip entire range partitions, reducing the data scanned.
Partition-wise joins at the hash level — because rows with the same hash key land in the same subpartition, the database can perform full or partial partition-wise joins in parallel, breaking a large join into smaller, faster operations.

Some SQL statements benefit from partition pruning on both dimensions simultaneously.

When to use range-hash partitioning

Data warehouse tables with join-heavy workloads

Range-hash partitioning is most common for very large tables that store historical data and are regularly joined with other large tables.

Use range-hash partitioning when:

The table stores time-series or historical data (for example, orders, events, or logs).
Queries frequently filter by a date or range column.
The table is often joined with other large tables on a high-cardinality key.

Information lifecycle management with tumbling windows

Range-hash partitioning also works well for tables that traditionally use hash partitioning but require tumbling windows for data lifecycle management. Over time, data in older range partitions can be:

Moved to a lower-cost storage layer
Compressed
Stored in read-only tables
Eventually cleared

Range partitioning is the standard approach for implementing tiered storage in Information Lifecycle Management (ILM) scenarios.

Example

The following DDL creates the TPCH orders table with range-hash partitioning. The table is partitioned by o_orderdate (range) and subpartitioned by o_custkey (hash, 64 subpartitions).

Range dimension (`o_orderdate`): queries that filter by order date benefit from partition pruning—only the relevant year partitions are scanned.
Hash dimension (`o_custkey`): queries that join orders with another large table on o_custkey can use partition-wise joins across the 64 subpartitions.

CREATE TABLE `orders` (
  `o_orderkey`      int(11)        NOT NULL,
  `o_custkey`       int(11)        NOT NULL,
  `o_orderstatus`   char(1)        DEFAULT NULL,
  `o_totalprice`    decimal(10,2)  DEFAULT NULL,
  `o_orderdate`     date           NOT NULL,
  `o_orderpriority` char(15)       DEFAULT NULL,
  `o_clerk`         char(15)       DEFAULT NULL,
  `o_shippriority`  int(11)        DEFAULT NULL,
  `o_comment`       varchar(79)    DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`, `o_orderdate`, `o_custkey`),
  KEY `o_orderkey`    (`o_orderkey`),
  KEY `i_o_custkey`   (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderdate`)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(o_orderdate)
SUBPARTITION BY HASH (`o_custkey`)
SUBPARTITIONS 64
(PARTITION item1 VALUES LESS THAN ('1992-01-01'),
 PARTITION item2 VALUES LESS THAN ('1993-01-01'),
 PARTITION item3 VALUES LESS THAN ('1994-01-01'),
 PARTITION item4 VALUES LESS THAN ('1995-01-01'),
 PARTITION item5 VALUES LESS THAN ('1996-01-01'),
 PARTITION item6 VALUES LESS THAN ('1997-01-01'),
 PARTITION item7 VALUES LESS THAN ('1998-01-01'),
 PARTITION item8 VALUES LESS THAN ('1999-01-01'),
 PARTITION item9 VALUES LESS THAN (MAXVALUE));