Create a LIST-RANGE partitioned table - PolarDB

List-range partitioning divides a table into LIST partitions based on discrete column values, then further divides each partition into RANGE subpartitions based on a numeric range. A common use case is partitioning sales data by region or department (LIST) and subdividing each partition by transaction amount or date (RANGE).

Syntax

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

partition_definition is:

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

subpartition_definition is:

SUBPARTITION subpartition_name
  VALUES LESS THAN {value | MAXVALUE}

Parameters

Parameter	Description
`table_name`	The name of the table.
`expr`	The partition expression. Must be of the INT type. String types are not supported.
`column_list`	The column list for `LIST COLUMNS()`. Expressions are not supported.
`value`	The boundary value of a subpartition.
`value_list`	The list of values for a LIST partition. Used in `LIST COLUMNS()`.
`MAXVALUE`	Catches all rows with values exceeding the last explicit boundary.
`partition_name`	The name of the partition. Must be unique within the table.
`subpartition_name`	The name of the subpartition. Must be unique across the entire table.

Examples

List-range partitioned table

The following example partitions a sales table by department number (LIST), then subpartitions each department group by transaction amount (RANGE).

CREATE TABLE sales_list_range
(
  dept_no     INT,
  part_no     INT,
  country     VARCHAR(20),
  date        DATE,
  amount      INT
)
PARTITION BY LIST (dept_no)
SUBPARTITION BY RANGE (amount)
(
  PARTITION p0 VALUES IN (1, 2) (
    SUBPARTITION s0 VALUES LESS THAN (1000),
    SUBPARTITION s1 VALUES LESS THAN (2000),
    SUBPARTITION s2 VALUES LESS THAN (3000),
    SUBPARTITION s3 VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION p1 VALUES IN (3, 4) (
    SUBPARTITION s4 VALUES LESS THAN (1000),
    SUBPARTITION s5 VALUES LESS THAN (2000),
    SUBPARTITION s6 VALUES LESS THAN (3000),
    SUBPARTITION s7 VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION p2 VALUES IN (5, 6) (
    SUBPARTITION s8  VALUES LESS THAN (1000),
    SUBPARTITION s9  VALUES LESS THAN (2000),
    SUBPARTITION s10 VALUES LESS THAN (3000),
    SUBPARTITION s11 VALUES LESS THAN (MAXVALUE)
  )
);

List columns-range partitioned table

Use LIST COLUMNS when the partition key is a non-integer column, such as a country name. The following example partitions sales data by country (LIST COLUMNS), then subpartitions each region by quarter (RANGE on the month number).

CREATE TABLE sales_list_columns_range
(
  dept_no     INT,
  part_no     INT,
  country     VARCHAR(20),
  date        DATE,
  amount      INT
)
PARTITION BY LIST COLUMNS (country)
SUBPARTITION BY RANGE (MONTH(date))
(
  PARTITION europe VALUES IN ('FRANCE', 'ITALY') (
    SUBPARTITION q1_2012 VALUES LESS THAN (4),
    SUBPARTITION q2_2012 VALUES LESS THAN (7),
    SUBPARTITION q3_2012 VALUES LESS THAN (10),
    SUBPARTITION q4_2012 VALUES LESS THAN (13)
  ),
  PARTITION asia VALUES IN ('INDIA', 'PAKISTAN') (
    SUBPARTITION q1_2013 VALUES LESS THAN (4),
    SUBPARTITION q2_2013 VALUES LESS THAN (7),
    SUBPARTITION q3_2013 VALUES LESS THAN (10),
    SUBPARTITION q4_2013 VALUES LESS THAN (13)
  ),
  PARTITION americas VALUES IN ('US', 'CANADA') (
    SUBPARTITION q1_2014 VALUES LESS THAN (4),
    SUBPARTITION q2_2014 VALUES LESS THAN (7),
    SUBPARTITION q3_2014 VALUES LESS THAN (10),
    SUBPARTITION q4_2014 VALUES LESS THAN (13)
  )
);