更新时间:2017-06-07 13:26
子分区是分区表中每个分区的再次分割。
语法
...
PARTITON BY RANGE(expr)
SUBPARTITION BY HASH(expr)
...
举例
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
)
);
几点要注意的语法项:
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
)
);
即便这个语句包含了一个 SUBPARTITIONS 2 子句,但是它仍然会执行失败。
每个 SUBPARTITION 子句必须包括 (至少)子分区的一个名字。否则,你可能要对该子分区设置任何你所需要的选项,或者允许该子分区对那些选项采用其默认的设置。
在每个分区内,子分区的名字必须是唯一的,但是在整个表中,没有必要保持唯一。例如,下面的 CREATE TABLE 语句是有效的:
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 s0,
SUBPARTITION s1
),
PARTITION p2 VALUES LESS THAN MAXVALUE
(
SUBPARTITION s0,
SUBPARTITION s1
)
);
在文档使用中是否遇到以下问题
更多建议
匿名提交