LIST-RANGE

Constraints

• The LIST type requires that value or value_list in the same or different partition definitions be unique.
• You can only insert or query the NULL value when it is contained in value. Otherwise, the NULL value does not comply with definitions and cannot be inserted.
• A subpartition in each partition can be considered as a new LIST partition. All rules and constraints are the same as those of LIST partitions. The definitions of subpartitions in different partitions can be different.

Syntax

The following statement is used to create one or more LIST-RANGE 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 RANGE {(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 LESS THAN {value | value_list | MAXVALUE}

Examples

• Create a LIST-RANGE partitioned table:

  CREATE TABLE tbl_list_range
  (
      col1 INT,
      col2 INT,
      col3 varchar(20),
      col4 DATE
  )
  PARTITION BY LIST (col1)
  SUBPARTITION BY RANGE(col2)
  (
    PARTITION p0 VALUES in (1, 2)(
      SUBPARTITION s0 VALUES LESS THAN(1000),
      SUBPARTITION s1 VALUES LESS THAN(2000)
    ),
    PARTITION p1 VALUES in (3, 4)(
      SUBPARTITION s2 VALUES LESS THAN(1000),
      SUBPARTITION s3 VALUES LESS THAN(MAXVALUE)
    ),
    PARTITION p2 VALUES in (5, 6)(
      SUBPARTITION s4 VALUES LESS THAN(3000),
      SUBPARTITION s5 VALUES LESS THAN(MAXVALUE)
    )
  );

• Create a LIST COLUMNS-RANGE partitioned table:

  CREATE TABLE tbl_list_columns_range
  (
      col1 INT,
      col2 INT,
      col3 varchar(20),
      col4 DATE
  )
  PARTITION BY LIST COLUMNS(col3)
  SUBPARTITION BY RANGE(month(col4))
  (
    PARTITION europe VALUES in ('FRANCE', 'ITALY')(
      SUBPARTITION q1_2012 VALUES LESS THAN(4),
      SUBPARTITION q2_2012 VALUES LESS THAN(7)
    ),
    PARTITION asia VALUES in ('INDIA', 'PAKISTAN')(
      SUBPARTITION q1_2013 VALUES LESS THAN(4),
      SUBPARTITION q2_2013 VALUES LESS THAN(7)
    ),
    PARTITION americas VALUES in ('US', 'CANADA')(
      SUBPARTITION q1_2014 VALUES LESS THAN(4),
      SUBPARTITION q2_2014 VALUES LESS THAN(7)
    )
  );