RANGE-RANGE

Constraints

• The RANGE type requires that the partition key value or value_list defined for each partition be monotonically increasing.
• MAXVALUE must be at the end.
• The NULL value is considered to be infinitely small. It is always inserted into the first partition definition.
• A subpartition in each partition can be considered as a new RANGE partition. All rules and constraints are the same as those of RANGE partitions.

Syntax

The following statement is used to create one or more RANGE-RANGE partitioned tables where each partition may contain one or more RANGE subpartitions:

CREATE TABLE ... PARTITION BY RANGE {(expr) | COLUMNS(column_list)}
   SUBPARTITION BY RANGE {(expr) | COLUMNS(column_list)}
[(partition_definition [, partition_definition] ...)];

partition_definition is:

PARTITION partition_name
       VALUES LESS THAN {(value | MAXVALUE | value_list) | MAXVALUE}
[(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition is:

SUBPARTITION subpartition_name
       VALUES LESS THAN {value | value_list | MAXVALUE}

Examples

• Create a RANGE-RANGE partitioned table:

  CREATE TABLE tbl_range_range (col1 INT, col2 INT, col3 varchar(20))
  PARTITION BY RANGE(col1)
  SUBPARTITION BY RANGE(col2)
  (
    PARTITION p0 VALUES LESS THAN (1000) (
      SUBPARTITION s0 VALUES LESS THAN(100),
      SUBPARTITION s1 VALUES LESS THAN(MAXVALUE)
    ),
    PARTITION p1 VALUES LESS THAN (2000)
    (
      SUBPARTITION s2 VALUES LESS THAN(100),
      SUBPARTITION s3 VALUES LESS THAN(200)
    ),
    PARTITION p2 VALUES LESS THAN (MAXVALUE)
    (
      SUBPARTITION s4 VALUES LESS THAN(200),
      SUBPARTITION s5 VALUES LESS THAN(400)
    )
  );

• Create a RANGE COLUMNS-RANGE partitioned table:

  CREATE TABLE tbl_range_col_range (col1 INT, col2 INT, col3 INT)
  PARTITION BY RANGE COLUMNS(col1, col2)
  SUBPARTITION BY RANGE(col3)
  (
    PARTITION p1 VALUES LESS THAN(1000, MAXVALUE)(
      SUBPARTITION s0 VALUES LESS THAN(100),
      SUBPARTITION s1 VALUES LESS THAN(MAXVALUE)
    ),
    PARTITION p2 VALUES LESS THAN(2000, MAXVALUE)(
      SUBPARTITION s2 VALUES LESS THAN(100),
      SUBPARTITION s3 VALUES LESS THAN(200)
    ),
    PARTITION p3 VALUES LESS THAN(MAXVALUE, MAXVALUE)(
      SUBPARTITION s4 VALUES LESS THAN(200),
      SUBPARTITION s5 VALUES LESS THAN(400)
    )
  );