AnalyticDB PostgreSQL版7.0分区表

分区表使用

将大表定义为分区表，从而将其分成较小的存储单元，根据查询条件，会只扫描满足条件的分区而避免全表扫描，从而显著提升查询性能。详细内容，请参见AnalyticDB PostgreSQL版6.0分区表。

创建范围(RANGE)分区表

您可以先创建范围（RANGE）分区表，再为该分区表添加子分区，例如：

-- 创建分区表
CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date);

-- 添加子分区
CREATE TABLE sales_20160101 PARTITION OF sales
FOR VALUES FROM('2016-01-01') TO ('2016-01-02');

您可以通过给出一个START值、一个END值以及一个定义分区增量值的子句让数据库自动产生分区。默认情况下，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 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); 

创建值 (LIST) 分区表

您可以先创建值（LIST）分区表，再为该分区表添加子分区，例如：

-- 创建分区表
CREATE TABLE rank (id int, rank int, year int, gender 
char(1), count int) 
DISTRIBUTED BY (id)
PARTITION BY LIST (gender);

-- 添加子分区
CREATE TABLE rank_girls PARTITION OF rank FOR VALUES IN ('F');

按列表分区的表可以使用同种数据类型列作为其分区键列。对于列表分区，您必须为每个要创建的分区（列表值）声明一个分区说明，例如：

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

创建哈希 (HASH) 分区表

哈希分区会基于分区键的哈希值决定数据所在的子分区。您可以先创建哈希 (HASH）分区表，再为该分区表添加子分区。为哈希分区表添加子分区时，MODULUS子句用于指定除数，REMAINDER子句用于指定哈希值被除后的余数。

-- 创建分区表
CREATE TABLE orders (id int, order_date date, customer_id int, amount decimal)
DISTRIBUTED BY (id)
PARTITION BY HASH (customer_id);

-- 添加子分区
CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 3);

云原生数据仓库AnalyticDB PostgreSQL版使用了内置的哈希函数对分区键值求取哈希值，提供了系统函数satisfies_hash_partition供您查询某个值是否在给定的子分区中。以下SQL语句为查询public.orders表中customer_id=2的数据是否在MODULUS 4, REMAINDER 0这个子分区内。

SELECT satisfies_hash_partition('public.orders'::regclass, 4, 0, variadic ARRAY[2]); 

创建表达式分区表

AnalyticDB PostgreSQL 7.0版支持表达式分区，更加灵活易用。

-- 创建分区表-- 
CREATE TABLE sales (id int, sale_time timestamp, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY LIST (date_trunc('month', sale_time));

-- 添加子分区
CREATE TABLE sales_202401 PARTITION OF sales FOR VALUES IN('2024-01-01'::timestamp);

创建多级分区表

支持创建多级的分区表。下述建表语句创建了具有三级表分区的表。一级分区在month字段上做RANGE分区，二级分区在region上做了LIST分区。

CREATE TABLE sales(id int, year int, month int, day int, region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (month)
  SUBPARTITION BY LIST (region)
    SUBPARTITION TEMPLATE (
    SUBPARTITION usa VALUES ('usa'),
    SUBPARTITION europe VALUES ('europe'),
    SUBPARTITION asia VALUES ('asia'),
    DEFAULT SUBPARTITION other_regions)
(START (1) END (13) EVERY (1), 
DEFAULT PARTITION other_months);

分区表查询优化

• 分区粒度

  通常分区表的定义都涉及到粒度问题，比如按时间分区，究竟是按天，按周，按月等。粒度越细，每张表的数据就越少，但是分区的数量就越多，反之亦然。关于分区的数量，没有绝对的标准，一般分区的数量在200左右已经算是比较多了。分区表数目过多，会有多方面的影响，比如查询优化器生成执行计划较慢，同时很多维护工作也会变慢，比如VACUUM等。

  对于多级分区表来说，分区文件的数量可能会增长得非常快。例如，如果一个表被按照月和城市划分并且有24个月以及100个城市，那么表分区的总数就是2400。特别对于列存表，会把每一列存在一个物理表中，因此如果这个表有100个列，系统就需要为该表管理十多万个文件。因此，在分区表设计之初首先需要考虑未来分区的总数，进而选择合理的分区定义。

• 分区裁剪

  云原生数据仓库AnalyticDB PostgreSQL版支持分区表的分区裁剪功能。详细信息，请参见分区裁剪。

分区表维护

分区表支持多种分区管理操作，包括新增分区，删除分区，重命名分区，清空分区，交换分区，分裂分区等，下面举例说明主要操作。

• 新增分区

  如果存在default 分区，则不能新增分区，只能分裂default分区，具体内容见下文分裂分区。

  CREATE TABLE sales_p2 PARTITION OF sales FOR VALUES FROM('2017-02-01') TO('2017-02-28');

  当分区表已有至少一个子分区时，也可以通过 ALTER TABLE ADD PARTITION 语法来新增分区。

  ALTER TABLE sales ADD partition p2 start ('2017-02-01') end ('2017-02-28');

• 挂载分区

  您可以将一张已有的表挂载在表结构相同的分区表的某个子分区上。

  ALTER TABLE sales ATTACH PARTITION sales_p2 FOR VALUES FROM('2017-02-01') TO('2017-02-28');

• 卸载分区

  卸载分区只是解除分区主表与子表的关系，不会真正删除分区子表。

  ALTER TABLE sales DETACH PARTITION sales_p2;

• 删除分区

  删除分区会直接删除分区子表。

  ALTER TABLE sales DROP PARTITION p2;

• 重命名分区

  ALTER TABLE sales RENAME PARTITION p2 TO Feb17;

• 清空分区

  ALTER TABLE sales TRUNCATE PARTITION p1;

• 交换分区

  ALTER TABLE sales EXCHANGE PARTITION p2 WITH TABLE {cos_table_name} ;

• 分裂分区

  -- 将分区p3 在 '2017-03-20' 左右切分成两块
  ALTER TABLE sales SPLIT partition p3 at ('2017-03-20') into (partition p2, partition p3);

  当已有default分区时，只能通过分裂default分区来新增分区。

  ALTER TABLE sales SPLIT DEFAULT PARTITION START('2017-03-01') END('2017-03-31') into(partition p3, default partition);