Create a KEY-KEY partitioned table - PolarDB

A key-key partitioned table uses KEY partitioning for both the primary partitions and subpartitions—a form of composite partitioning where each primary partition is further divided into subpartitions using a second KEY expression.

Syntax

CREATE TABLE [schema.]table_name
  table_definition
    PARTITION BY [LINEAR] KEY(expr) [PARTITIONS num]
    SUBPARTITION BY [LINEAR] KEY(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 partition expression. Must be INT type. String type is not supported.
`partition_name`	The partition name. Must be unique within the table.
`subpartition_name`	The subpartition name. Must be unique within the table.

Example

The following example creates a key-key partitioned table with 3 primary partitions on dept_no and 2 subpartitions per partition on part_no, resulting in 6 subpartitions total (3 × 2 = 6).

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

Usage notes

INT type only: The expr column for both PARTITION BY KEY and SUBPARTITION BY KEY must be INT type. String type columns are not supported.
Unique names: Both partition names and subpartition names must be unique within the table.
LINEAR option: Add the LINEAR keyword to use a linear hashing algorithm.