RANGE-LIST

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 RANGE-LIST partitioned tables where each partition may contain one or more subpartitions:

CREATE TABLE ... PARTITION BY RANGE {(expr) | COLUMNS(column_list)}
   SUBPARTITION BY LIST {(expr) | COLUMNS(column_list)}
[(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 IN {(value | value_list)}

Examples

• Create a RANGE-LIST partitioned table:

  CREATE TABLE tbl_range_list (col1 INT, col2 INT, col3 varchar(20))
  PARTITION BY RANGE(col1)
      SUBPARTITION BY LIST(col2)
  (
    PARTITION m1 VALUES LESS THAN(1000) (
      SUBPARTITION p0 VALUES in (1, 2),
  	SUBPARTITION p1 VALUES in (3, 4),
  	SUBPARTITION p2 VALUES in (5, 6)
    ),
    PARTITION m2 VALUES LESS THAN(2000) (
      SUBPARTITION p3 VALUES in (1, 2),
  	SUBPARTITION p4 VALUES in (3, 4),
  	SUBPARTITION p5 VALUES in (5, 6)
    ),
    PARTITION m3 VALUES LESS THAN(MAXVALUE) (
      SUBPARTITION p6 VALUES in (1, 2),
  	SUBPARTITION p7 VALUES in (3, 4),
  	SUBPARTITION p8 VALUES in (5, 6)
    )
  );

• Create a RANGE COLUMNS-LIST partitioned table:

  CREATE TABLE tbl_range_columns_list
  (
      col1 INT,
      col2 INT,
      col3 varchar(20),
      col4 DATE
  )
  PARTITION BY RANGE COLUMNS(col4)
  	SUBPARTITION BY LIST(col1)
  (
    PARTITION dp1 VALUES LESS THAN('2023-01-01')(
      SUBPARTITION p0 VALUES in (1, 2),
      SUBPARTITION p1 VALUES in (3, 4),
      SUBPARTITION p2 VALUES in (5, 6)
    ),
    PARTITION dp2 VALUES LESS THAN('2024-01-01')(
      SUBPARTITION p3 VALUES in (1, 2),
      SUBPARTITION p4 VALUES in (3, 4),
      SUBPARTITION p5 VALUES in (5, 6)
    ),
    PARTITION dp3 VALUES LESS THAN('2025-01-01')(
      SUBPARTITION p6 VALUES in (1, 2),
      SUBPARTITION p7 VALUES in (3, 4),
      SUBPARTITION p8 VALUES in (5, 6)
    )
  );