This topic describes how to create a range-list partitioned table.
Syntax
The following statement is used to create one or more range-list partitioned table where each partition may contain one or more subpartitions:
CREATE TABLE ... PARTITION BY RANGE {(expr) | COLUMNS(column_list)}
SUBPARTITION BY LIST(expr)
[(partition_definition [, partition_definition] ...)];partition_definition is:
PARTITION partition_name
VALUES LESS THAN {(value | value_list) | MAXVALUE}
[(subpartition_definition [, subpartition_definition] ...)]subpartition_definition is:
SUBPARTITION subpartition_name
VALUES IN (value_list2)Parameters
Parameter | Description |
expr | The expression of the partition. It must be the INT type. The string type is not supported. |
column_list | The list of partition key columns. It is used in RANGE COLUMNS(). Expressions are not supported. |
value | The boundary value of the partition. |
value_list | The list of the values of the partition key columns. It is used in RANGE COLUMNS(). |
value_list2 | The list of the boundary values. |
MAXVALUE | The maximum value of the partition. |
partition_name | The name of the partition. The name must be unique within the table. |
subpartition_name | The name of the subpartition. The name must be unique within the table. |
Examples
Create a range-list partitioned table:
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:
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)
)
);