云数据库 OceanBase中的子分区

子分区是分区表中每个分区的再次分割。
[backcolor=transparent]语法 ...
PARTITON BY RANGE(expr)
     SUBPARTITION BY HASH(expr)
    ...

[backcolor=transparent]举例 CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(TO_DAYS(purchased))
SUBPARTITIONS 2
(
     PARTITION p0 VALUES LESS THAN (1990),
     PARTITION p1 VALUES LESS THAN (2000),
     PARTITION p2 VALUES LESS THAN MAXVALUE
)；

表 ts 有 3 个 RANGE 分区。这 3 个分区中的每一个分区 p0, p1 和 p2 又被进一步分成了 2 个子分区。实际上，整个表被分成了 3 * 2 = 6 个分区。但是，由于 PARTITION BY RANGE 子句的作用，这些分区的头 2 个只保存“purchased”列中值小于 1990 的那些记录。等价于： CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(TO_DAYS(purchased))
(
     PARTITION p0 VALUES LESS THAN (1990)
          (
             SUBPARTITION s0,
             SUBPARTITION s1
         ),
         PARTITION p1 VALUES LESS THAN (2000)
         (
             SUBPARTITION s2,
             SUBPARTITION s3
         ),
         PARTITION p2 VALUES LESS THAN MAXVALUE
         (
             SUBPARTITION s4,
             SUBPARTITION s5
         )
);

几点要注意的语法项：

• 每个分区必须有相同数量的子分区。
• 如果在一个分区表上的任何分区上使用 SUBPARTITION 来明确定义任何子分区，那么就必须定义所有的子分区。换句话说，下面的语句将执行失败：CREATE TABLE ts (id INT, purchased DATE)
• PARTITION BY RANGE(YEAR(purchased))
• SUBPARTITION BY HASH(TO_DAYS(purchased))
• (
•      PARTITION p0 VALUES LESS THAN (1990)
•      (
•          SUBPARTITION s0,
•          SUBPARTITION s1
•      ),
•      PARTITION p1 VALUES LESS THAN (2000),
•      PARTITION p2 VALUES LESS THAN MAXVALUE
•      (
•          SUBPARTITION s2,
•          SUBPARTITION s3
•      )
• )；
•