This topic describes how to create a range-hash partitioned table.
Syntax
The following statement is used to create one or more range-hash partitioned tables. Partitions are of the RANGE [COLUMNS] type. Subpartitions are of the HASH or KEY type.
CREATE TABLE [ schema. ]table_name
table_definition
PARTITION BY RANGE {(expr) COLUMNS(column_list)}
SUBPARTITION BY {[LINEAR] HASH(expr) [SUBPARTITIONS number]
[LINEAR] KEY [ALGORITHM={1 2}] (column_list)}
(partition_definition [, partition_definition] ...);
partition_definition is:
PARTITION partition_name
VALUES LESS THAN {(expr value_list) MAXVALUE}
(subpartition_definition [, subpartition_definition] ...)
subpartition_definition is:
SUBPARTITION subpartition_name
Parameters
Parameter | Description |
expr | The expression of the partition. It must be of the INT type. The string type is not supported. |
column_list | The list of partition key columns. Expressions are not supported. |
number | The number of subpartitions. |
value | The boundary value of the partition. |
value_list | The list of the boundary values of the partitions. 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-hash partitioned table:
CREATE TABLE sales_range_hash
(
s_id varchar(20),
dept_no INT,
part_no INT,
country varchar(20),
date DATE,
amount INT,
primary key (s_id, date)
)
PARTITION BY RANGE COLUMNS(date)
SUBPARTITION BY KEY(s_id) SUBPARTITIONS 3
(
PARTITION p1 VALUES LESS THAN('2023-01-01'),
PARTITION p2 VALUES LESS THAN('2023-02-01'),
PARTITION p3 VALUES LESS THAN('2023-03-01'),
PARTITION p4 VALUES LESS THAN('2023-04-01')
);