Greenplum中每个表都需要有一个分布键，如果你建表的时候没有显示使用语法DISTRIBUTED BY (column) 指定一个分布键，系统也会默认为你指定一个。分布目的是把数据打散到每个节点，打散的规则是hash或者randomly（类似于分片）。这样在计算时可以充分利用每个节点的资源进行并行计算。

分区特性在本文会详细介绍，两者比较如下：

数据分布是在物理上拆分表数据，将数据打散到各个节点，使数据可以并行计算，这在Greenplum中是必须的。
表分区是在逻辑上拆分大表的数据提高查询性能，也有利于数据生命周期的管理，这在Greenplum中是可选的。
无论是分区表还是非分区表，在Greenplum中，数据都是分散到各个节点上的。
分区不会影响数据在各个节点上的分布情况。

什么时候使用分区表

是否使用分区表，可以通过以下几个方面进行考虑：

表数据量是否足够大：通常对于大的事实表，比如数据量有几千万或者过亿，我们可以考虑使用分区表，但数据量大小并没有一个绝对的标准可以使用，一般是根据经验，以及对目前性能是否满意。
表是否有合适的分区字段：如果数据量足够大了，这个时候我们就需要看下是否有合适的字段能够用来分区，通常如果数据有时间维度，比如按天，按月等，是比较理想的分区字段。
表内数据是否具有生命周期：通常数仓中的数据不可能一直存放，一般都会有一定的生命周期，比如最近一年等，这里就涉及到对旧数据的管理，如果有分区表，就很容易删除旧的数据，或者将旧的数据归档到对象存储等更为廉价的存储介质上。
查询语句中是否含有分区字段：如果你对一个表做了分区，但是所有的查询都不带分区字段，这不仅无法提高性能反而会使性能下降，因为所有的查询都会扫描所有的分区表。

Greenplum数据库中的表分区

Greenplum数据库把表划分成部分（也称为分区）来启用大规模并行处理。表分区在使用PARTITION BY（以及可选的SUBPARTITION BY）子句的CREATE TABLE执行期间进行。分区操作会创建一个顶层（父）表以及一层或者多层子表。在内部，Greenplum数据库会在顶层表和它的底层分区之间创建继承关系，类似于PostgreSQL的INHERITS子句的功能。

Greenplum使用表创建时定义的分区标准来创建每一个分区及其上一个可区分的CHECK约束，这个约束限制了该表能含有的数据。查询优化器使用CHECK约束来决定要扫描哪些表分区来满足一个给定的查询谓词。

Greenplum系统目录存储了分区层次信息，这样插入到顶层父表的行会被正确地传播到子表分区。要更改分区设计或者表结构，可使用带有PARTITION子句的ALTER TABLE修改父表。

要把数据插入到一个分过区的表中，用户需要指定根分区表，也就是用CREATE TABLE命令创建的那个表。用户也可以在INSERT命令中指定分区表的一个叶子子表。如果该数据对于指定的叶子子表不合法，则会返回一个错误。不支持在DML命令中指定一个非叶子或者非根分区表。

决定表的分区策略

Greenplum数据库不支持对复制表进行分区(DISTRIBUTED REPLICATED)。不是所有的哈希分布表或随机分布表都适合于分区。如果下列问题的答案全部或者大部分都是yes，表分区就是一种可行的改进查询性能的数据库设计策略。如果下列问题的答案大部分都是no，表分区对于该表就不是正确的方案。请测试用户的设计策略来确保查询性能能得到预期的改进。

表是否足够大？大型的事实表是进行表划分很好的候选。如果在一个表中有几百万或者几十亿个记录，从逻辑上将数据分成较小的块会让用户在性能方面受益。对于只有几千行或者更少数据的小表来说，维护分区的管理开销将会超过用户可能得到的性能收益。
用户是否体验到不满意的性能？正如任何性能调节的动机一样，只有针对一个表的查询产生比预期还要慢的响应时间时才应该对该表分区。
用户的查询谓词有没有可识别的访问模式？检查用户的查询负载的WHERE子句并且查找一直被用来访问数据的表列。例如，如果大部分查询都倾向于用日期查找记录，那么按月或者按周的日期分区设计可能会对用户有益。或者如果用户倾向于根据地区访问记录，可考虑一种列表分区设计来根据地区划分表。
用户的数据仓库是否维护了一个历史数据的窗口？ 另一个分区设计的考虑是用户的组织对维护历史数据的业务需求。例如，用户的数据仓库可能要求用户保留过去十二个月的数据。如果数据按月分区，用户可以轻易地从仓库中删除最旧的月份分区并且把当前数据载入到最近的月份分区中。
数据能否基于某种定义的原则被划分成差不多相等的部分？尽可能选择将把用户的数据均匀划分的分区原则。如果分区包含基本同等数量的记录，查询性能会基于创建的分区数量而提升。例如，通过将一个大型表划分成10个分区，一个查询的执行速度将比在未分区表上快10倍，前提是这些分区就是为支持该查询的条件而设计。

不要创建超过所需数量的分区。创建过多的分区可能会拖慢管理和维护工作，例如清理、恢复Segment、扩展集群、检查磁盘用量等等。

除非查询优化器能基于查询谓词排除一些分区，否则分区技术不能改进查询性能。每个分区都扫描的查询运行起来会比表没有分区时还慢，因此如果用户的查询中很少能排除分区，请避免进行分区。请检查查询的解释计划来确认分区被排除。参考查询分析获取更多关于分区的信息。

Warning: 请对多级分区格外谨慎，因为分区文件的数量可能会增长得非常快。例如，如果一个表被按照日和城市划分并且有1,000个日以及1,000个城市，那么分区的总数就是一百万。列存表会把每一列存在一个物理表中，因此如果这个表有100个列，系统就需要为该表管理一亿个文件。

在选定一种多级分区策略之前，可以考虑一种带有位图索引的单级分区。索引会降低数据装载的速度，因此推荐用用户的数据和模式进行性能测试以决定最佳的策略。

创建分区表

Greenplum中创建分区表可以直接使用create table（关键字PARTITION BY）创建，也可以通过继承的方式（关键字INHERITS）来创建分区表。

在使用CREATE TABLE创建表时就可以对它们分区。这个主题提供了用于创建带有数个分区的表的SQL语法的例子。

对一个表分区：

决定分区设计：日期范围、数字范围或者值的列表。
选择要按哪个（哪些）列对表分区。
决定用户需要多少个分区级别。例如，用户可以按月创建一个日期范围分区表，然后对每个月的分区按照销售地区划分子分区。

定义日期范围分区表

一个按日期范围分区的表使用单个date或者timestamp列作为分区键列。如果需要，用户可以使用同一个分区键列来创建子分区，例如按月分区然后按日建子分区。请考虑使用最细的粒度分区。例如，对于一个用日期分区的表，用户可以按日分区并且得到365个每日的分区，而不是先按年分区然后按月建子分区再然后按日建子分区。一种多级设计可能会减少查询规划时间，但是一种平面的分区设计运行得更快。

用户可以通过给出一个START值、一个END值以及一个定义分区增量值的EVERY子句让Greenplum数据库自动产生分区。默认情况下，START值总是被包括在内而END值总是被排除在外。例如：

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2016-01-01') INCLUSIVE
   END (date '2017-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)

( START (date '2016-01-01') INCLUSIVE

END (date '2017-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 Jan16 START (date '2016-01-01') INCLUSIVE , 
  PARTITION Feb16 START (date '2016-02-01') INCLUSIVE ,
  PARTITION Mar16 START (date '2016-03-01') INCLUSIVE ,
  PARTITION Apr16 START (date '2016-04-01') INCLUSIVE ,
  PARTITION May16 START (date '2016-05-01') INCLUSIVE ,
  PARTITION Jun16 START (date '2016-06-01') INCLUSIVE ,
  PARTITION Jul16 START (date '2016-07-01') INCLUSIVE ,
  PARTITION Aug16 START (date '2016-08-01') INCLUSIVE ,
  PARTITION Sep16 START (date '2016-09-01') INCLUSIVE ,
  PARTITION Oct16 START (date '2016-10-01') INCLUSIVE ,
  PARTITION Nov16 START (date '2016-11-01') INCLUSIVE ,
  PARTITION Dec16 START (date '2016-12-01') INCLUSIVE 
                  END (date '2017-01-01') EXCLUSIVE );

CREATE TABLE sales (id int, date date, amt decimal(10,2))

DISTRIBUTED BY (id)

PARTITION BY RANGE (date)

( PARTITION Jan16 START (date '2016-01-01') INCLUSIVE ,

PARTITION Feb16 START (date '2016-02-01') INCLUSIVE ,

PARTITION Mar16 START (date '2016-03-01') INCLUSIVE ,

PARTITION Apr16 START (date '2016-04-01') INCLUSIVE ,

PARTITION May16 START (date '2016-05-01') INCLUSIVE ,

PARTITION Jun16 START (date '2016-06-01') INCLUSIVE ,

PARTITION Jul16 START (date '2016-07-01') INCLUSIVE ,

PARTITION Aug16 START (date '2016-08-01') INCLUSIVE ,

PARTITION Sep16 START (date '2016-09-01') INCLUSIVE ,

PARTITION Oct16 START (date '2016-10-01') INCLUSIVE ,

PARTITION Nov16 START (date '2016-11-01') INCLUSIVE ,

PARTITION Dec16 START (date '2016-12-01') INCLUSIVE

END (date '2017-01-01') EXCLUSIVE );

用户不需要为每一个分区声明一个END子句，只需要为最后一个分区写上就好。在这个例子中，Jan16会在Feb16开始处结束。

定义数字范围分区表

一个按数字范围分区的表使用单个数字数据类型列作为分区键列。例如：

CREATE TABLE rank (id int, rank int, year int, gender 
char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( START (2006) END (2016) EVERY (1), 
  DEFAULT PARTITION extra );

CREATE TABLE rank (id int, rank int, year int, gender

char(1), count int)

DISTRIBUTED BY (id)

PARTITION BY RANGE (year)

( START (2006) END (2016) 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 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 );

Note: 当前的Greenplum数据库传统优化器允许列表分区带有多列（组合）分区键。一个范围分区只允许单一列作为分区键。 Greenplum查询优化器不支持组合键，因此用户不能使用组合分区键。

下图展示了用户的 sales 表首先被分布到两个节点，然后每个节点又按照某个标准进行了分区。分区的主要目的是实现分区裁剪以通过降低数据访问量来提高性能。分区裁剪指根据查询条件，优化器自动把不需要访问的分区过滤掉，以降低查询执行时的数据扫描量。PostgreSQL 支持静态条件分区裁剪，Greenplum 通过 ORCA 优化器实现了动态分区裁剪。动态分区裁剪可以提升十几倍至数百倍性能。

更多有关默认分区的信息，请见增加默认分区。

定义多级分区表

用户可以用分区的子分区创建一种多级分区设计。使用一个子分区模板可以确保每一个分区都有相同的子分区设计，包括用户后来增加的分区。例如，下面的SQL创建所示的两级分区设计Figure 1：

CREATE TABLE sales (trans_id int, date date, amount 
decimal(9,2), region text) 
DISTRIBUTED BY (trans_id)
PARTITION BY RANGE (date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'), 
  SUBPARTITION asia VALUES ('asia'), 
  SUBPARTITION europe VALUES ('europe'), 
  DEFAULT SUBPARTITION other_regions)
  (START (date '2011-01-01') INCLUSIVE
   END (date '2012-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 month'), 
   DEFAULT PARTITION outlying_dates );

CREATE TABLE sales (trans_id int, date date, amount

decimal(9,2), region text)

DISTRIBUTED BY (trans_id)

PARTITION BY RANGE (date)

SUBPARTITION BY LIST (region)

SUBPARTITION TEMPLATE

( SUBPARTITION usa VALUES ('usa'),

SUBPARTITION asia VALUES ('asia'),

SUBPARTITION europe VALUES ('europe'),

DEFAULT SUBPARTITION other_regions)

(START (date '2011-01-01') INCLUSIVE

END (date '2012-01-01') EXCLUSIVE

EVERY (INTERVAL '1 month'),

DEFAULT PARTITION outlying_dates );

下面的例子展示了一个三级分区设计，其中sales表被按照year分区，然后按照month分区，再然后按照region分区。 SUBPARTITION TEMPLATE子句保证每一个年度的分区都有相同的子分区结构。这个例子在该层次的每一个级别上都声明了一个DEFAULT分区。

CREATE TABLE p3_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 (2012) EVERY (1), 
  DEFAULT PARTITION outlying_years );

CREATE TABLE p3_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 (2012) EVERY (1),

DEFAULT PARTITION outlying_years );

CAUTION:

当用户创建基于范围的多级分区时，很容易会创建大量的子分区，有一些包含很少的甚至不包含数据。这可能会在系统表中增加很多项，这些项增加了优化和执行查询所需的时间和内存。增加范围区间或者选择一种不同的分区策略可减少创建的子分区数量。

对已有的表进行分区

表只能在创建时被分区。如果用户有一个表想要分区，用户必须创建一个分过区的表，把原始表的数据载入到新表，再删除原始表并且把分过区的表重命名为原始表的名称。用户还必须重新授权表上的权限。例如：

CREATE TABLE sales5 (LIKE sales) 
PARTITION BY RANGE (date)
( START (date '2016-01-01') INCLUSIVE
   END (date '2017-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 month') );

INSERT INTO sales5 SELECT * FROM sales;
DROP TABLE sales;
ALTER TABLE sales5 RENAME TO sales;
GRANT ALL PRIVILEGES ON sales TO admin;
GRANT SELECT ON sales TO guest;

CREATE TABLE sales5 (LIKE sales)

PARTITION BY RANGE (date)

( START (date '2016-01-01') INCLUSIVE

END (date '2017-01-01') EXCLUSIVE

EVERY (INTERVAL '1 month') );

INSERT INTO sales5 SELECT * FROM sales;

DROP TABLE sales;

ALTER TABLE sales5 RENAME TO sales;

GRANT ALL PRIVILEGES ON sales TO admin;

GRANT SELECT ON sales TO guest;

分区表的限制

对于每个分区级别，一个已分区的表最多能有32,767个分区。

一个已分区表上的主键或者唯一约束必须包含所有的分区列。一个唯一索引可以忽略分区列，但是它只能在已分区表的每个部分而不是整个已分区的表上被强制。

用DISTRIBUTED REPLICATED分布策略创建的表不能被分区。

Greenplum的下一代查询优化器GPORCA支持统一的多级分区表。如果启用了GPORCA（默认情况）并且多级分区表不统一，Greenplum数据库会用传统查询优化器对该表执行查询。有关统一多级分区表的信息，请见关于统一多级分区表。

有关交换叶节点和外部表的信息，请参考用外部表交换叶子子分区。

当一个叶子子分区是外部表时，对分区表有一些限制：

针对包含外部表分区的分区表运行的查询将用传统查询优化器执行。
外部表分区是一个只读外部表。尝试在该外部表分区中访问或者修改数据的命令会返回一个错误。例如：
- 尝试在外部表分区中改变数据的INSERT、DELETE以及UPDATE命令会返回一个错误。
- TRUNCATE命令返回一个错误。
- COPY命令无法复制数据到一个会更新外部表分区的分区表中。
- 尝试从一个外部表分区中复制出数据的COPY命令会返回一个错误，除非用户为COPY命令指定IGNORE EXTERNAL PARTITIONS子句。如果用户指定该子句，数据不会被从外部表分区复制出来。
要对一个有外部表作为叶子子表的分区表使用COPY命令，可以使用一个SQL查询来拷贝数据。例如，如果表my_sales包含一个外部表作为叶子子表，这个命令可以把其数据发送到stdout：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！
验证码：
请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。
相关文章
打赏赞(1)分享

标签：原创 GreenPlum 继承分区表

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

2024年 4月
一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

嘿，我是小麦，需要帮助随时找我哦。

18509239930
个人微信
DB宝
个人邮箱
点击加入QQ群
个人微店
回到顶部

原 GreenPlum中的分区表

简介

关于表分区

分区与分布的区别

什么时候使用分区表

Greenplum数据库中的表分区

决定表的分区策略

创建分区表

定义日期范围分区表

定义数字范围分区表

定义列表分区表

定义多级分区表

对已有的表进行分区

分区表的限制

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

原 GreenPlum中的分区表

简介

关于表分区

分区与分布的区别

什么时候使用分区表

Greenplum数据库中的表分区

决定表的分区策略

创建分区表

定义日期范围分区表

定义数字范围分区表

定义列表分区表

定义多级分区表

对已有的表进行分区

分区表的限制

相关文章

您可能还喜欢...

利用嵌套docker在单个容器中安装GreenPlum多主机环境

MySQL登陆报错 ERROR 1040 (HY000) Too many connections 超过最大连接数错误

【视频12c~19c OCP】第1节--课程简介及注意事项

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复