Updated on 2024-09-06 GMT+08:00

LIST-LIST

Syntax

The following statement is used to create one or more LIST-LIST partitioned tables 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) | COLUMNS(column_list)}
   (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)
Table 1 Parameters

Parameter

Description

table_name

The name of the table to be created.

expr

The expression of the partition. Currently, only the INT type is supported.

column_list

The list of partition key columns. It is used in LIST COLUMNS(). Expressions are not supported.

value_list

The list of the values of the partition key columns. 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 tbl_list_list
    (
        col1 INT,
        col2 INT,
        col3 varchar(20),
        col4 DATE
    )
    PARTITION BY LIST (col1)
    SUBPARTITION BY LIST (col2)
    (
      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 tbl_list_columns_list
    (
        col1 INT,
        col2 INT,
        col3 varchar(20),
        col4 DATE
    )
    PARTITION BY LIST COLUMNS(col3)
    SUBPARTITION BY LIST (col1)
    (
       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)
       )
    );