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

创建RANGE-HASH分区表

语法

创建RANGE-HASH分区表。一级分区是RANGE [COLUMNS] 类型，二级分区是HASH/KEY类型。

CREATE TABLE [ schema. ]table_name
 table_definition
   PARTITION BY RANGE {(expr) | COLUMNS(column_list)}
   SUBPARTITION BY {[LINEAR] HASH(expr) [SUBPARTITIONS number]
   | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)}
   (partition_definition [, partition_definition] ...);

其中，partition_definition为

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

subpartition_definition为

SUBPARTITION subpartition_name

参数

示例

RANGE-HASH类型的使用示例：

CREATE TABLE sales_range_hash
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE(month(date))
SUBPARTITION BY HASH(TO_DAYS(date)) SUBPARTITIONS 2
(
  PARTITION q1_2012 VALUES LESS THAN(4),
  PARTITION q2_2012 VALUES LESS THAN(7),
  PARTITION q3_2012 VALUES LESS THAN(10),
  PARTITION q4_2012 VALUES LESS THAN(13)
);

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

CREATE TABLE sales_range_columns_hash
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE COLUMNS(dept_no, part_no)
SUBPARTITION BY HASH(TO_DAYS(date)) SUBPARTITIONS 2
(
  PARTITION dp1 VALUES LESS THAN(1000, MAXVALUE),
  PARTITION dp2 VALUES LESS THAN(2000, MAXVALUE),
  PARTITION dp3 VALUES LESS THAN(3000, MAXVALUE),
  PARTITION dp4 VALUES LESS THAN(4000, MAXVALUE)
);

创建LIST-HASH分区

语法

创建LIST-HASH分区表。一级分区是LIST [COLUMNS]类型，二级分区是HASH/KEY类型。

CREATE TABLE [ schema. ]table_name
 table_definition
   PARTITION BY LIST {(expr) | COLUMNS(column_list)}
   SUBPARTITION BY {[LINEAR] HASH(expr) [SUBPARTITIONS number]
   | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)}
   (partition_definition [, partition_definition] ...);

其中，partition_definition为

 PARTITION partition_name
        VALUES IN ( value_list)
        (subpartition_definition [, subpartition_definition] ...)

subpartition_definition为

SUBPARTITION subpartition_name

参数

示例

LIST-HASH类型的使用示例：

CREATE TABLE sales_list_hash
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY LIST(amount)
SUBPARTITION BY HASH(TO_DAYS(date)) SUBPARTITIONS 2
(
  PARTITION p0 VALUES in (1, 2),
  PARTITION p1 VALUES in (3, 4),
  PARTITION p2 VALUES in (5, 6)
);

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

CREATE TABLE sales_list_columns_hash
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY LIST COLUMNS(country)
SUBPARTITION BY HASH(TO_DAYS(date))
(
  PARTITION europe VALUES in ('FRANCE', 'ITALY')(
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
  PARTITION asia VALUES in ('INDIA', 'PAKISTAN')(
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
  PARTITION americas VALUES in ('US', 'CANADA')(
            SUBPARTITION s4,
            SUBPARTITION s5
        )
);