GP--表分区管理(二)

1、定义日期范围分区表

日期范围分区表使用单个date或者timestamp字段作为分区键。

可以通过使用START值、 END值和EVERY子句定义分区增量让GPDB自动产生分区。

缺省情况下， START值总是被包含而END值总是被排除（左闭右开）。

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

2、定义数字范围分区表

数字范围分区表使用单个数字列作为分区键。例如：

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 
);

3、定义列表分区表

列表分区表可以使用任何数据类型的列作为分区键，分区规则使用等值比较。
列表分区可以使用多个COLUMN(组合起来)作为分区键，而范围分区只允许使用单独COLUMN作为分区键。对于列表分区，必须为每个分区指定相应的值。

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 
);

4、定义多级分区表

使用SUBPARTITION TEMPLATE来确保每个分区具有相同的子分区结构，尤其是对那些后增加的分区来说。

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 '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE
  EVERY (INTERVAL '1 month'), 
  DEFAULT PARTITION outlying_dates 
);

下面是一个3级分区表的例子，这里表sales被分区为年、月、区域。 SUBPARTITION TEMPLATE子句确保每个年分区有相同的子分区结构。
另外，每个级别的分区都有一个默认分区：

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的时候做分区。要想对现有的表做分区，只能重新创建一个分区表、重新装载数据到新的分区表中、删掉旧表然后把新的分区表改为旧表的名称。还必须重新对TABLE做授权。

CREATE TABLE sales2 (LIKE sales)
PARTITION BY RANGE (date)
( START (date '1998-01-01') INCLUSIVE END (date '2002-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;

分区表的限制

主键或者唯一约束必须包含表上的所有分区键。而唯一索引可以不包含分区键，但是，其只对一个分区强制有效，而不是对整个分区表有效。

装载分区表

一旦创建了分区表，顶级表总是空的。数据值储存在最低层的表中。在多级分区表中，仅仅在层级最低的子分区中有数据。

在运行期间，查询规划器会扫描整个TABLE的层级结构并使用CHECK约束适配查询条件来决定哪些子表需要被扫描。

默认分区(只要该层级中存在)总是会被扫描。如果默认分区中包含数据，其会拖慢整体的扫表时间。

如果有必要，还可以直接把数据装载到子表中。还可以先创建一个中间表、装载数据、然后与分区表进行分区交换。这种分区交换的性能高于直接的COPY和INSERT。

查看分区设计

要查看分区表的设计情况，通过pg_partitions视图查看。

SELECT 
partitionboundary
, partitiontablename
, partitionname
, partitionlevel
, partitionrank
FROM pg_partitions WHERE tablename='sales2';
 
pg_partition_templates - 用以创建SUBPARTITION的SUBPARTITION template
pg_partition_columns – 用于分区的分区键

维护分区表

必须使用ALTER TABLE命令从顶级表来维护分区。

1. 最常见的场景是根据日期范围的设计来维护数据时，删除旧分区并添加一个新的分区。
2. 把旧的分区交换为压缩AO表以节省空间。

若在父表中存在默认分区，添加分区的操作只能是从默认分区拆分出一个新的分区。

由于分区不要求有名称，若分区没有名称，下面的表达式仍可以指定一个分区:

PARTITION FOR (value) or PARTITION FOR(RANK(number))

(1) 添加新分区

如果原有的分区表包含了subpartition template设计，新增的分区将根据该模版创建子分区。

CREATE TABLE sales (trans_id int, date char(8), 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 ('2008-01-01') INCLUSIVE END ('2009-01-01') EXCLUSIVE
  EVERY (INTERVAL '1 month'), 
  DEFAULT PARTITION outlying_dates 
);
 
ALTER TABLE sales ADD PARTITION
START (date '2009-02-01') INCLUSIVE
END (date '2009-03-01') EXCLUSIVE;

如果在创建TABLE时没有subpartition template，在新增分区时需要定义子分区：

ALTER TABLE sales ADD PARTITION
START (date '2009-02-01') INCLUSIVE
END (date '2009-03-01') EXCLUSIVE
( SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'),
SUBPARTITION europe VALUES ('europe') );

子表的名称格式如下：

<父表名称>_<分区层级>prt<分区名称>

子表的名称不能通过直接执行ALTER表名来实现。但修改顶级表的名称，该改变将会影响所有相关的分区表。

添加缺省分区

ALTER TABLE sales ADD DEFAULT PARTITION other;

如果是多级分区表， 同一层次中的每个分区都需要一个默认分区。

ALTER TABLE sales ALTER PARTITION FOR (RANK(1)) ADD DEFAULT PARTITION other;
ALTER TABLE sales ALTER PARTITION FOR (RANK(2)) ADD DEFAULT PARTITION other;
ALTER TABLE sales ALTER PARTITION FOR (RANK(3)) ADD DEFAULT PARTITION other;

RANK(partitionrank)指的是范围分区同一层级中的顺序。partitionrank可参见pg_partition表。

(2)删除分区

ALTER TABLE sales DROP PARTITION FOR (RANK(1));

注意： 在将RANK(1)的分区删除后，其余分区的partitionrank值仍然是从1开始的连续编号。 编号的顺序按照分区字段的值由小到大从1开始排序。
不管分区是否连续(中间有值不匹配分区)，或者随意的修改分区定义。

(3)清空分区数据

在清空一个包含子分区的分区时，其所有相关子分区的数据都自动被清空。

ALTER TABLE sales TRUNCATE PARTITION FOR (RANK(1));

(4)交换分区：

CREATE TABLE jan08 (LIKE sales) WITH (appendonly=true);
INSERT INTO jan08 SELECT * FROM sales_1_prt_1 ;
ALTER TABLE sales EXCHANGE PARTITION FOR (DATE '2008-01-01') WITH TABLE jan08

(5)拆分分区

拆分分区是将现有的一个分区分成两个分区。 使用ALTER TABLE命令来拆分分区。
只能拆分最低层级的分区表(只有包含数据的分区可以拆分)。
指定的分割值对应的数据将进入后面一个分区(就是STAER为INCLUSIVE)。

ALTER TABLE sales SPLIT PARTITION FOR ('2008-01-01')
AT ('2008-01-16') INTO (PARTITION jan081to15, PARTITION jan0816to31);

如果分区表有默认分区，要添加新的分区只能从默认分区拆分。而且只能从最低层级分区的默认分区拆分(只有包含数据的分区可以拆分)。
在使用INTO子句时，第2个分区名称必须是已经存在的默认分区。

ALTER TABLE sales SPLIT DEFAULT PARTITION
START ('2009-01-01') INCLUSIVE
END ('2009-02-01') EXCLUSIVE
INTO (PARTITION jan09, default partition);

(6)修改子分区模版

使用ALTER TABLE SET SUBPARTITION TEMPLATE命令来修改现有分区表的子分区模版。 在修改了子分区模版之后添加的分区，其子分区将按照新的模版产生。已经存在的分区不会被修改。

ALTER TABLE sales SET SUBPARTITION TEMPLATE
(   SUBPARTITION usa VALUES ('usa'),
    SUBPARTITION asia VALUES ('asia'),
    SUBPARTITION europe VALUES ('europe'),
    SUBPARTITION africa VALUES ('africa')
    DEFAULT SUBPARTITION other 
);
ALTER TABLE sales ADD PARTITION sales_prt_3
START ('2009-03-01') INCLUSIVE END ('2009-04-01') EXCLUSIVE;

这个例子在一级分区有默认分区时是不能执行的，要查看效果，先删除默认分区。
要删除子分区模版，使用SET SUBPARTITION TEMPLATE并使用空的参数来完成。

ALTER TABLE sales SET SUBPARTITION TEMPLATE ();