Create LIST-LIST partitioned tables - PolarDB

LIST-LIST partitioning applies list partitioning along two independent dimensions—partition by one discrete column, then subpartition by another. This is useful when data naturally maps to two sets of discrete values, such as department and sales channel, where you want direct control over which values land in each partition and subpartition.

Syntax

CREATE TABLE [schema.]table_name
  table_definition
  PARTITION BY LIST {(expr) | COLUMNS(column_list)}
  SUBPARTITION BY LIST(expr)
  (partition_definition [, partition_definition] ...);

partition_definition:

PARTITION partition_name
  VALUES IN (value_list)
  (subpartition_definition [, subpartition_definition] ...)

subpartition_definition:

SUBPARTITION subpartition_name
  VALUES IN (value_list)

Parameters

Parameter	Description
`table_name`	The name of the table to create.
`expr`	The partition expression. Must be INT type. String type is not supported.
`column_list`	The column list used in `LIST COLUMNS()`. Expressions are not supported.
`value_list`	The list of boundary values for the partition.
`partition_name`	The partition name. Must be unique within the table.
`subpartition_name`	The subpartition name. Must be unique within the table.

Examples

LIST-LIST partitioned table

The following example partitions a sales table by department (dept_no) at the partition level and by part number (part_no) at the subpartition level. A row with dept_no = 1 and part_no = 3 is first routed to partition p0 (which holds dept_no values 1 and 2), then further routed to subpartition partno1 (which holds part_no values 3 and 4).

CREATE TABLE sales_list_list
(
  dept_no     INT,
  part_no     INT,
  country     VARCHAR(20),
  date        DATE,
  amount      INT
)
PARTITION BY LIST (dept_no)
SUBPARTITION BY LIST (part_no)
(
  PARTITION p0 VALUES IN (1, 2)(
    SUBPARTITION partno0 VALUES IN (1, 2),
    SUBPARTITION partno1 VALUES IN (3, 4),
    SUBPARTITION partno2 VALUES IN (5, 6)
  ),
  PARTITION p1 VALUES IN (3, 4)(
    SUBPARTITION partno3 VALUES IN (1, 2),
    SUBPARTITION partno4 VALUES IN (3, 4),
    SUBPARTITION partno5 VALUES IN (5, 6)
  ),
  PARTITION p2 VALUES IN (5, 6)(
    SUBPARTITION partno6 VALUES IN (1, 2),
    SUBPARTITION partno7 VALUES IN (3, 4),
    SUBPARTITION partno8 VALUES IN (5, 6)
  )
);

LIST COLUMNS-LIST partitioned table

The following example uses LIST COLUMNS to partition by a string column (country) at the partition level and by an integer column (dept_no) at the subpartition level. LIST COLUMNS accepts column values directly without requiring an INT expression.

CREATE TABLE sales_list_columns_list
(
  dept_no     INT,
  part_no     INT,
  country     VARCHAR(20),
  date        DATE,
  amount      INT
)
PARTITION BY LIST COLUMNS(country)
SUBPARTITION BY LIST (dept_no)
(
  PARTITION europe VALUES IN ('FRANCE', 'ITALY')(
    SUBPARTITION p0 VALUES IN (1, 2),
    SUBPARTITION p1 VALUES IN (3, 4),
    SUBPARTITION p2 VALUES IN (5, 6)
  ),
  PARTITION asia VALUES IN ('INDIA', 'PAKISTAN')(
    SUBPARTITION p3 VALUES IN (1, 2),
    SUBPARTITION p4 VALUES IN (3, 4),
    SUBPARTITION p5 VALUES IN (5, 6)
  ),
  PARTITION americas VALUES IN ('US', 'CANADA')(
    SUBPARTITION p6 VALUES IN (1, 2),
    SUBPARTITION p7 VALUES IN (3, 4),
    SUBPARTITION p8 VALUES IN (5, 6)
  )
);