This topic describes how to create a list-list partitioned table.
Syntax
The following statement is used to create one or more list-list partitioned table where each partition may contain one or more subpartitions:
CREATE TABLE [ schema. ]table_name
table_definition
PARTITION BY LIST {(expr) | COLUMNS(column_list)}
SUBPARTITION BY LIST(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 IN (value_list)Parameters
Parameter | Description |
table_name | The name of the table to be created. |
expr | The expression of the partition. It must be of the INT type. The string type is not supported. |
column_list | The list of partitions. It is used in LIST COLUMNS(). Expressions are not supported. |
value_list | The list of the boundary values of the partitions. It is used in LIST COLUMNS(). |
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 list-list partitioned table:
CREATE TABLE sales_list_list
(
dept_no INT,
part_no INT,
country varchar(20),
date DATE,
amount INT
)
PARTITION BY LIST (dept_no)
SUBPARTITION BY LIST (part_no)
(
PARTITION p0 VALUES in (1, 2)(
SUBPARTITION partno0 VALUES in (1, 2),
SUBPARTITION partno1 VALUES in (3, 4),
SUBPARTITION partno2 VALUES in (5, 6)
),
PARTITION p1 VALUES in (3, 4)(
SUBPARTITION partno3 VALUES in (1, 2),
SUBPARTITION partno4 VALUES in (3, 4),
SUBPARTITION partno5 VALUES in (5, 6)
),
PARTITION p2 VALUES in (5, 6)(
SUBPARTITION partno6 VALUES in (1, 2),
SUBPARTITION partno7 VALUES in (3, 4),
SUBPARTITION partno8 VALUES in (5, 6)
)
);Create a list columns-list partitioned table:
CREATE TABLE sales_list_columns_list
(
dept_no INT,
part_no INT,
country varchar(20),
date DATE,
amount INT
)
PARTITION BY LIST COLUMNS(country)
SUBPARTITION BY LIST (dept_no)
(
PARTITION europe VALUES in ('FRANCE', 'ITALY')(
SUBPARTITION p0 VALUES in (1, 2),
SUBPARTITION p1 VALUES in (3, 4),
SUBPARTITION p2 VALUES in (5, 6)
),
PARTITION asia VALUES in ('INDIA', 'PAKISTAN')(
SUBPARTITION p3 VALUES in (1, 2),
SUBPARTITION p4 VALUES in (3, 4),
SUBPARTITION p5 VALUES in (5, 6)
),
PARTITION americas VALUES in ('US', 'CANADA')(
SUBPARTITION p6 VALUES in (1, 2),
SUBPARTITION p7 VALUES in (3, 4),
SUBPARTITION p8 VALUES in (5, 6)
)
);