创建RANGE-RANGE和RANGE-LIST分区表

创建RANGE-RANGE分区表

语法

创建一个或多个RANGE-RANGE分区表，其中每个分区可能有一个或一个以上的子分区：

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

其中，partition_definition为：

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

其中，subpartition_definition为：

SUBPARTITION subpartition_name
       VALUES LESS THAN {value | MAXVALUE}

参数

示例

RANGE-RANGE类型的使用示例：

CREATE TABLE sales_range_range
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE(year(date))
SUBPARTITION BY RANGE(month(date))
(
  PARTITION p_2020 VALUES LESS THAN (2021) (
    SUBPARTITION q1_2020 VALUES LESS THAN(4),
    SUBPARTITION q2_2020 VALUES LESS THAN(7),
    SUBPARTITION q3_2020 VALUES LESS THAN(10),
    SUBPARTITION q4_2020 VALUES LESS THAN(13)
  ),
  PARTITION p_2021 VALUES LESS THAN (2022)
  (
    SUBPARTITION q1_2021 VALUES LESS THAN(4),
    SUBPARTITION q2_2021 VALUES LESS THAN(7),
    SUBPARTITION q3_2021 VALUES LESS THAN(10),
    SUBPARTITION q4_2021 VALUES LESS THAN(13)
  ),
  PARTITION p_2022 VALUES LESS THAN (2023)
  (
    SUBPARTITION q1_2022 VALUES LESS THAN(4),
    SUBPARTITION q2_2022 VALUES LESS THAN(7),
    SUBPARTITION q3_2022 VALUES LESS THAN(10),
    SUBPARTITION q4_2022 VALUES LESS THAN(13)
  )
);

RANGE COLUMNS-RANGE类型的使用示例：

CREATE TABLE sales_range_columns_range
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE COLUMNS(dept_no, part_no)
SUBPARTITION BY RANGE(month(date))
(
  PARTITION p1 VALUES LESS THAN(1000, MAXVALUE)(
    SUBPARTITION q1_2019 VALUES LESS THAN(4),
    SUBPARTITION q2_2019 VALUES LESS THAN(7),
    SUBPARTITION q3_2019 VALUES LESS THAN(10),
    SUBPARTITION q4_2019 VALUES LESS THAN(13)
  ),
  PARTITION p2 VALUES LESS THAN(2000, MAXVALUE)(
    SUBPARTITION q1_2020 VALUES LESS THAN(4),
    SUBPARTITION q2_2020 VALUES LESS THAN(7),
    SUBPARTITION q3_2020 VALUES LESS THAN(10),
    SUBPARTITION q4_2020 VALUES LESS THAN(13)
  ),
  PARTITION p3 VALUES LESS THAN(3000, MAXVALUE)(
    SUBPARTITION q1_2021 VALUES LESS THAN(4),
    SUBPARTITION q2_2021 VALUES LESS THAN(7),
    SUBPARTITION q3_2021 VALUES LESS THAN(10),
    SUBPARTITION q4_2021 VALUES LESS THAN(13)
  ),
  PARTITION p4 VALUES LESS THAN(4000, MAXVALUE)(
    SUBPARTITION q1_2022 VALUES LESS THAN(4),
    SUBPARTITION q2_2022 VALUES LESS THAN(7),
    SUBPARTITION q3_2022 VALUES LESS THAN(10),
    SUBPARTITION q4_2022 VALUES LESS THAN(13)
  )
);

创建RANGE-LIST分区表

语法

创建一个或多个RANGE-LIST分区表，其中每个分区可能有一个或一个以上的子分区：

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

其中，partition_definition为：

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

其中，subpartition_definition为：

SUBPARTITION subpartition_name
       VALUES IN (value_list2)

参数

示例

RANGE-LIST类型的使用示例：

CREATE TABLE sales_range_list
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE(month(date))
    SUBPARTITION BY LIST(amount)
(
  PARTITION q1_2012 VALUES LESS THAN(4) (
        SUBPARTITION p0 VALUES in (1, 2),
        SUBPARTITION p1 VALUES in (3, 4),
        SUBPARTITION p2 VALUES in (5, 6)
  ),
  PARTITION q2_2012 VALUES LESS THAN(7) (
        SUBPARTITION p3 VALUES in (1, 2),
        SUBPARTITION p4 VALUES in (3, 4),
        SUBPARTITION p5 VALUES in (5, 6)
  ),
  PARTITION q3_2012 VALUES LESS THAN(10) (
        SUBPARTITION p6 VALUES in (1, 2),
        SUBPARTITION p7 VALUES in (3, 4),
        SUBPARTITION p8 VALUES in (5, 6)
  ),
  PARTITION q4_2012 VALUES LESS THAN(13) (
        SUBPARTITION p9 VALUES in (1, 2),
        SUBPARTITION p10 VALUES in (3, 4),
        SUBPARTITION p11 VALUES in (5, 6)
  )
);

RANGE COLUMNS-LIST类型的使用示例：

CREATE TABLE sales_range_columns_list
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE COLUMNS(dept_no, part_no)
    SUBPARTITION BY LIST(amount)
(
  PARTITION dp1 VALUES LESS THAN(1000, MAXVALUE)(
            SUBPARTITION p0 VALUES in (1, 2),
            SUBPARTITION p1 VALUES in (3, 4),
            SUBPARTITION p2 VALUES in (5, 6)
  ),
  PARTITION dp2 VALUES LESS THAN(2000, MAXVALUE)(
            SUBPARTITION p3 VALUES in (1, 2),
            SUBPARTITION p4 VALUES in (3, 4),
            SUBPARTITION p5 VALUES in (5, 6)
  ),
  PARTITION dp3 VALUES LESS THAN(3000, MAXVALUE)(
            SUBPARTITION p6 VALUES in (1, 2),
            SUBPARTITION p7 VALUES in (3, 4),
            SUBPARTITION p8 VALUES in (5, 6)
  ),
  PARTITION dp4 VALUES LESS THAN(4000, MAXVALUE)(
            SUBPARTITION p9 VALUES in (1, 2),
            SUBPARTITION p10 VALUES in (3, 4),
            SUBPARTITION p11 VALUES in (5, 6)
  )
);