分区表

声明式创建分区

按列创建分区PARTITION BY LIST

将学员表student按所在城市使用partition by list创建分区

创建分区表基表

创建格式

create table 表名字段名 数据类型PARTITION BY LIST要分区的字段名

 

创建子分区表子表

create table 子分区名 partition of 基表名 for values in ('分区列的值1','分区列的值2’);

 create table 子分区名 PARTITION OF student default; #按默认

 

插入测试数据、执行数据查询、查看SQL执行计划

 

按范围分区PARTITION BY RANGE

将学员表student按报名时间使用PARTITION BY RANGE创建分区

创建分区表基表

create table 表名字段名 数据类型PARTITION BY RANGE要分区的字段名

 

创建子分区子表

 create table 子分区名 PARTITION OF 分区名(基表名) FOR VALUES FROM (起始时间) TO ('终止时间');

 create table 子分区名 PARTITION OF student default; #按默认

使用Hash分区PARTITION BY HASH

将学员表student按学员编号使用PARTITION BY HASH创建分区表

创建分区表基表

create table 表名字段名 数据类型PARTITION BY HASH要分区的字段名

 

创建子分区表子表

 create table 子分区名 PARTITION OF 分区名(基表名)  FOR VALUES WITH (MODULUS 取模的数量, REMAINDER 取余结果);

 

插入测试数据、执行数据查询、查看SQL执行计划

 

通过表继承和触发器创建分表

创建父表

 

创建子表

create table 子表名(CHECK(reg_date>='日期起始' and reg_date<'日期终止')) INHERITS(要继承的表名);

 

查看父表状态信息

 

创建触发器函数

创建触发器

 

插入测试数据

查询测试

 

分区表注意事项

1、插入数据时按分区定义将数据存储在对应的分区中如果插入的数据找不到任何满足定义条件分区会导致数据插入失败。因此需要定义一个默认的分区用于存储非固定属性的数据。

2、查询整个分区表时SQL根据查询条件执行分析后会自动跳过不需要扫描的分区。在某些特殊场景下也可以显示指定要查询的分区。

3、创建子分区后通常需要为每个分区创建单独索引提高查询性能。

分区表扫描控制

1. 如果只查询分区表某个分区的数据KES可能仍会扫描所有分区这会降低查询性能。KES提供了参数constraint_exclusion从而避免扫描素有分区。
2. 参数constraint_exclusion控制查询规划器对表约束的使用可以优化查询。