GP支持分区表,主要用来存放大表,如fact table
目的:
1. 把大数据切片,便于查询
2. 便于数据库维护
分区创建时,每个分区会自带一个Check约束,来限定数据的范围。Check约束也用于 执行查询时定位分区。
支持分区类型:
1. 范围分区 range partition
2. 列表分区 list partition
3. 组合分区
![Greenplum介绍 <wbr>- <wbr>Partitioned <wbr>Table Greenplum介绍 <wbr>- <wbr>Partitioned <wbr>Table](http://s14.sinaimg.cn/middle/90474a92gbea60414516e&690)
(多级分区表)
-------partition 和 distribution的区别------
distribution -- 物理上拆分表数据、能并行执行查询
partition -- 逻辑上拆分大表数据提高查询性能、利于数据仓库维护工作
------表分区策略------
@ 表是否足够大?
大事实表可以选择分区。如果一个表有几百万或10亿数据量,你可以看到性能上的优势。
如果只是几千行或者更小的表的话,dba将在维护分区花费大量的精力,小表不要建分区,直接full table scan。
@ 是否对现有性能不满意?
当表的查询响应时间比预期要慢的话,可以考虑用分区表
@ 是否能够判断查询可以使用固定范围或固定值来做限定条件?
where子句中是否使用分区字段。
例如:经常按日志来查询,可以按月或周来范围分区。或按地区来查的话,可以按地区来做列表分区。
@ 是否需要维护你数据仓库中的大量历史记录?
分区设计另外一个考量是根据业务要求来决定历史数据的保存。
例如:数据仓库只要保留过去12个月的数据。如果是按月分区的话,你很容易删掉历史分区并载入数据到当前几个月份中。
@ 是否能基于一些定义规则将数据分成等份?
你应该选择尽可能的将数据等分。如果分区中包含等数量记录,则查询性能提升基于已创建的分区数量。
例如:一个大表被分成10个分区,执行一个查询将比非分区表快10倍。
------创建分区表------
[时间范围分区]
2中创建方式:
==》CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2008-01-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE
EVERY (INTERVAL '1 day') ); --时间间隔
==> CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( PARTITION Jan08 START (date '2008-01-01') INCLUSIVE ,
PARTITION Feb08 START (date '2008-02-01') INCLUSIVE ,
PARTITION Mar08 START (date '2008-03-01') INCLUSIVE ,
PARTITION Apr08 START (date '2008-04-01') INCLUSIVE ,
PARTITION May08 START (date '2008-05-01') INCLUSIVE ,
PARTITION Jun08 START (date '2008-06-01') INCLUSIVE ,
PARTITION Jul08 START (date '2008-07-01') INCLUSIVE ,
PARTITION Aug08 START (date '2008-08-01') INCLUSIVE ,
PARTITION Sep08 START (date '2008-09-01') INCLUSIVE ,
PARTITION Oct08 START (date '2008-10-01') INCLUSIVE ,
PARTITION Nov08 START (date '2008-11-01') INCLUSIVE ,
PARTITION Dec08 START (date '2008-12-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE ); --在最后要写个end
[数字范围分区]
==>CREATE TABLE rank (id int, rank int, year int, gender
char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( START (2001) END (2008) EVERY (1),
DEFAULT PARTITION extra );
[列表分区]
==>CREATE TABLE rank (id int, rank int, year int, gender
char(1), count int )
DISTRIBUTED BY (id)
PARTITION BY LIST (gender) --可以使用多列
( PARTITION girls VALUES ('F'),
PARTITION boys VALUES ('M'),
DEFAULT PARTITION other );
------多级分区------
==>CREATE TABLE sales (id int, year int, month int, day int,
region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
SUBPARTITION BY RANGE (month)
SUBPARTITION TEMPLATE (
START (1) END (13) EVERY (1),
DEFAULT SUBPARTITION other_months )
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
SUBPARTITION usa VALUES ('usa'),
SUBPARTITION europe VALUES ('europe'),
SUBPARTITION asia VALUES ('asia'),
DEFAULT SUBPARTITION other_regions )
( START (2002) END (2010) EVERY (1),
DEFAULT PARTITION outlying_years );
------普通表 转 分区表 -------
==>CREATE TABLE sales2 (LIKE sales)
PARTITION BY RANGE (date)
( START (date '2008-01-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE
EVERY (INTERVAL '1 month') );
==>INSERT INTO sales2 SELECT * FROM sales;
==>DROP TABLE sales;
==>ALTER TABLE sales2 RENAME TO sales;
==>GRANT ALL PRIVILEGES ON sales TO admin;
==>GRANT SELECT ON sales TO guest;
------选择性分区扫描的一些限制-----
限制条件:
1. 条件中使用简单直接的限制操作符(如:= < <= > >= <>),可以选择性扫描分区
2. 不能识别变化性函数,只能是固定值
例如:where语句中 date > current_date可以做范围扫描。 但是time > timeofday则不行。
3. 不确定的值是无法做runtime评估,必须要指定明确的值
例如:SELECT * from partition_table PT, other_table OT WHERE
PT.id=OT.id and OT.some_column = 'value';
--value必须指定值
------查看表分区-------
pg_partitions - 查看创建分区信息
pg_partition_templates - 查看是用subpartition模板创建的子分区
pg_partition_column - 查看分区字段
关于修改表分区的语法,参考官方文档。