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

创建LIST-RANGE分区表

语法

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

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

其中，partition_definition为：

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

其中，subpartition_definition为：

SUBPARTITION subpartition_name
      VALUES LESS THAN {value | MAXVALUE}

参数

示例

LIST-RANGE类型的使用示例：

CREATE TABLE sales_list_range
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY LIST (amount)
SUBPARTITION BY RANGE(month(date))
(
  PARTITION p0 VALUES in (1, 2)(
    SUBPARTITION q1_2012 VALUES LESS THAN(4),
    SUBPARTITION q2_2012 VALUES LESS THAN(7),
    SUBPARTITION q3_2012 VALUES LESS THAN(10),
    SUBPARTITION q4_2012 VALUES LESS THAN(13)
  ),
  PARTITION p1 VALUES in (3, 4)(
    SUBPARTITION q1_2013 VALUES LESS THAN(4),
    SUBPARTITION q2_2013 VALUES LESS THAN(7),
    SUBPARTITION q3_2013 VALUES LESS THAN(10),
    SUBPARTITION q4_2013 VALUES LESS THAN(13)
  ),
  PARTITION p2 VALUES in (5, 6)(
    SUBPARTITION q1_2014 VALUES LESS THAN(4),
    SUBPARTITION q2_2014 VALUES LESS THAN(7),
    SUBPARTITION q3_2014 VALUES LESS THAN(10),
    SUBPARTITION q4_2014 VALUES LESS THAN(13)
  )
);

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

CREATE TABLE sales_list_columns_range
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY LIST COLUMNS(country)
SUBPARTITION BY RANGE(month(date))
(
  PARTITION europe VALUES in ('FRANCE', 'ITALY')(
    SUBPARTITION q1_2012 VALUES LESS THAN(4),
    SUBPARTITION q2_2012 VALUES LESS THAN(7),
    SUBPARTITION q3_2012 VALUES LESS THAN(10),
    SUBPARTITION q4_2012 VALUES LESS THAN(13)
  ),
  PARTITION asia VALUES in ('INDIA', 'PAKISTAN')(
    SUBPARTITION q1_2013 VALUES LESS THAN(4),
    SUBPARTITION q2_2013 VALUES LESS THAN(7),
    SUBPARTITION q3_2013 VALUES LESS THAN(10),
    SUBPARTITION q4_2013 VALUES LESS THAN(13)
  ),
  PARTITION americas VALUES in ('US', 'CANADA')(
    SUBPARTITION q1_2014 VALUES LESS THAN(4),
    SUBPARTITION q2_2014 VALUES LESS THAN(7),
    SUBPARTITION q3_2014 VALUES LESS THAN(10),
    SUBPARTITION q4_2014 VALUES LESS THAN(13)
  )
);

创建LIST-LIST分区表

语法

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

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

其中，partition_definition为：

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

其中，subpartition_definition为：

SUBPARTITION subpartition_name
         VALUES IN (value_list)

参数

示例

LIST-LIST类型的使用示例：

CREATE TABLE sales_list_list
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY LIST (amount)
SUBPARTITION BY LIST (part_no)
(
  PARTITION p0 VALUES in (1, 2)(
    SUBPARTITION partno0 VALUES in (1, 2),
    SUBPARTITION partno1 VALUES in (3, 4),
    SUBPARTITION partno2 VALUES in (5, 6)
  ),
  PARTITION p1 VALUES in (3, 4)(
    SUBPARTITION partno3 VALUES in (1, 2),
    SUBPARTITION partno4 VALUES in (3, 4),
    SUBPARTITION partno5 VALUES in (5, 6)
  ),
  PARTITION p2 VALUES in (5, 6)(
    SUBPARTITION partno6 VALUES in (1, 2),
    SUBPARTITION partno7 VALUES in (3, 4),
    SUBPARTITION partno8 VALUES in (5, 6)
  )
);

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

CREATE TABLE sales_list_columns_list
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY LIST COLUMNS(country)
SUBPARTITION BY LIST (amount)
(
   PARTITION europe VALUES in ('FRANCE', 'ITALY')(
    SUBPARTITION p0 VALUES in (1, 2),
    SUBPARTITION p1 VALUES in (3, 4),
    SUBPARTITION p2 VALUES in (5, 6)
   ),
   PARTITION asia VALUES in ('INDIA', 'PAKISTAN')(
    SUBPARTITION p3 VALUES in (1, 2),
    SUBPARTITION p4 VALUES in (3, 4),
    SUBPARTITION p5 VALUES in (5, 6)
   ),
   PARTITION americas VALUES in ('US', 'CANADA')(
    SUBPARTITION p6 VALUES in (1, 2),
    SUBPARTITION p7 VALUES in (3, 4),
    SUBPARTITION p8 VALUES in (5, 6)
   )
);