This topic describes how to create a range-range partitioned table.
Syntax
The following statement is used to create one or more range-range partitioned table where each partition may contain one or more subpartitions:
CREATE TABLE ... PARTITION BY RANGE {(expr) | COLUMNS(column_list)}
SUBPARTITION BY RANGE(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 LESS THAN {value | MAXVALUE}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(). |
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-range partitioned table:
CREATE TABLE sales_range_range
(
dept_no INT,
part_no INT,
country varchar(20),
date DATE,
amount INT
)
PARTITION BY RANGE(dept_no)
SUBPARTITION BY RANGE(part_no)
(
PARTITION p0 VALUES LESS THAN (1000) (
SUBPARTITION s0 VALUES LESS THAN(100),
SUBPARTITION s1 VALUES LESS THAN(200),
SUBPARTITION s2 VALUES LESS THAN(300),
SUBPARTITION s3 VALUES LESS THAN(MAXVALUE)
),
PARTITION p1 VALUES LESS THAN (2000)
(
SUBPARTITION s4 VALUES LESS THAN(100),
SUBPARTITION s5 VALUES LESS THAN(200),
SUBPARTITION s6 VALUES LESS THAN(300),
SUBPARTITION s7 VALUES LESS THAN(MAXVALUE)
),
PARTITION p2 VALUES LESS THAN (MAXVALUE)
(
SUBPARTITION s8 VALUES LESS THAN(100),
SUBPARTITION s9 VALUES LESS THAN(200),
SUBPARTITION s10 VALUES LESS THAN(300),
SUBPARTITION s11 VALUES LESS THAN(MAXVALUE)
)
);Create a range columns-range partitioned table:
CREATE TABLE sales_range_columns_range
(
dept_no INT,
part_no INT,
country varchar(20),
date DATE,
amount INT
)
PARTITION BY RANGE COLUMNS(dept_no, part_no)
SUBPARTITION BY RANGE(amount)
(
PARTITION p1 VALUES LESS THAN(1000, MAXVALUE)(
SUBPARTITION s0 VALUES LESS THAN(100),
SUBPARTITION s1 VALUES LESS THAN(200),
SUBPARTITION s2 VALUES LESS THAN(300),
SUBPARTITION s3 VALUES LESS THAN(MAXVALUE)
),
PARTITION p2 VALUES LESS THAN(2000, MAXVALUE)(
SUBPARTITION s4 VALUES LESS THAN(100),
SUBPARTITION s5 VALUES LESS THAN(200),
SUBPARTITION s6 VALUES LESS THAN(300),
SUBPARTITION s7 VALUES LESS THAN(MAXVALUE)
),
PARTITION p3 VALUES LESS THAN(MAXVALUE, MAXVALUE)(
SUBPARTITION s8 VALUES LESS THAN(100),
SUBPARTITION s9 VALUES LESS THAN(200),
SUBPARTITION s10 VALUES LESS THAN(300),
SUBPARTITION s11 VALUES LESS THAN(MAXVALUE)
)
);