Create a range-list partitioned table

RANGE-LIST partitioning uses RANGE (or RANGE COLUMNS) as the primary partition strategy and LIST as the subpartition strategy. Each range partition contains one or more list subpartitions, letting you organize rows by a numeric or date range at the top level and then by a discrete set of values within each range.

Syntax

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

partition_definition:

PARTITION partition_name
       VALUES LESS THAN {(value | value_list) | MAXVALUE}
[(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:

SUBPARTITION subpartition_name
       VALUES IN (value_list2)

Parameters

Parameter	Description
`expr`	The partition expression. Must be INT. String types are not supported.
`column_list`	The list of partition key columns used in `RANGE COLUMNS()`. Expressions are not supported.
`value`	The boundary value of the partition.
`value_list`	The list of partition key column values used in `RANGE COLUMNS()`.
`value_list2`	The list of boundary values for a subpartition.
`MAXVALUE`	The maximum value of 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

The following table partitions sales data by amount (RANGE) and then by dept_no (LIST):

CREATE TABLE sales_range_list
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE(amount)
  SUBPARTITION BY LIST(dept_no)
(
  PARTITION m1 VALUES LESS THAN(1000) (
        SUBPARTITION p0 VALUES IN (1, 2),
        SUBPARTITION p1 VALUES IN (3, 4),
        SUBPARTITION p2 VALUES IN (5, 6)
  ),
  PARTITION m2 VALUES LESS THAN(2000) (
        SUBPARTITION p3 VALUES IN (1, 2),
        SUBPARTITION p4 VALUES IN (3, 4),
        SUBPARTITION p5 VALUES IN (5, 6)
  ),
  PARTITION m3 VALUES LESS THAN(MAXVALUE) (
        SUBPARTITION p6 VALUES IN (1, 2),
        SUBPARTITION p7 VALUES IN (3, 4),
        SUBPARTITION p8 VALUES IN (5, 6)
  )
);

Create a RANGE COLUMNS-LIST partitioned table

The following table uses RANGE COLUMNS on a DATE column, which allows non-integer partition keys:

CREATE TABLE sales_range_columns_list
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE COLUMNS(date)
  SUBPARTITION BY LIST(dept_no)
(
  PARTITION dp1 VALUES LESS THAN('2023-01-01') (
        SUBPARTITION p0 VALUES IN (1, 2),
        SUBPARTITION p1 VALUES IN (3, 4),
        SUBPARTITION p2 VALUES IN (5, 6)
  ),
  PARTITION dp2 VALUES LESS THAN('2024-01-01') (
        SUBPARTITION p3 VALUES IN (1, 2),
        SUBPARTITION p4 VALUES IN (3, 4),
        SUBPARTITION p5 VALUES IN (5, 6)
  ),
  PARTITION dp3 VALUES LESS THAN('2025-01-01') (
        SUBPARTITION p6 VALUES IN (1, 2),
        SUBPARTITION p7 VALUES IN (3, 4),
        SUBPARTITION p8 VALUES IN (5, 6)
  )
);