Create a hash-hash partitioned table - PolarDB - Alibaba Cloud Documentation Center

Hash-hash partitioning divides a table into partitions using a hash function, then divides each partition into subpartitions using a second hash function.

Syntax

CREATE TABLE [schema.]table_name
  table_definition
  PARTITION BY [LINEAR] HASH(expr) [PARTITIONS num]
  SUBPARTITION BY [LINEAR] HASH(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 partitioning expression. Must return an INT value. String types are not supported.
`num`	The number of partitions. Valid only for hash or key partitions.
`sub_num`	The number of subpartitions. Valid only for hash or key subpartitions.
`partition_name`	The name of a partition. Must be unique within the table.
`subpartition_name`	The name of a subpartition. Must be unique within the table.

Examples

Implicit subpartition naming

The simplest form lets PolarDB name the subpartitions automatically:

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