云原生数据仓库AnalyticDB：表分区定义

支持的表分区类型

• 范围（RANGE）分区：基于一个数值型范围划分数据，例如按日期区间定义。

• 值（LIST）分区：基于一个值列表划分数据，例如按城市属性定义。

• 多级分区表：范围分区和值分区的多级组合。

创建范围（RANGE）分区表

您可以指定一个起始值（START）、一个结束值（END）以及一个定义分区增量值的子句让数据库自动产生分区。默认情况下，起始值总是在当前分区中而结束值总是在下个分区中。

创建一个按日期范围分区的表，示例SQL如下：

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

创建一个按数字范围分区的表，例如使用int类型的列作为分区键列，示例SQL如下：

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分区表可以使用任意允许值比较的列作为分区键列。创建LIST分区表时，您必须要为每一个分区声明每一个值分区。

创建LIST分区表示例如下：

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

创建多级分区表

AnalyticDB PostgreSQL版支持创建多级分区表。以下建表示例将创建一个具有三级表分区的表，其中一级分区在year字段上进行了RANGE分区，二级分区在month字段上进行了RANGE分区，三级分区在region字段上进行了LIST分区，示例如下：

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

增加一个分区

您可以通过ALTER TABLE语句为分区表增加一个分区。如果创建分区表时使用了子分区模板，那么新增的分区也会根据该模板划分子分区。增加一个分区的示例如下：

ALTER TABLE sales ADD PARTITION 
            START (date '2017-02-01') INCLUSIVE 
            END (date '2017-03-01') EXCLUSIVE;

如果创建分区表时没有使用子分区模板，您可以在增加分区时定义子分区，增加一个分区并定义子分区的示例如下：

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

如果需要为现有分区添加一个子分区，您可以指定要更改的分区，示例SQL如下：

ALTER TABLE sales ALTER PARTITION FOR (RANK(12))
      ADD PARTITION africa VALUES ('africa');

指定分区子表表名

AnalyticDB PostgreSQL版在6.3.10.9版本之后支持在创建分区表的时候指定分区表子表名，您可以在创建分区子表时添加WITH(tablename=<tablename_1>)子句指定分区子表表名。

示例SQL如下：

CREATE TABLE partition_with_name_list (a int, b int, c int) DISTRIBUTED BY (a) PARTITION BY LIST (a)
(
    PARTITION p1 VALUES (1)  WITH (tablename='partition_with_name_list_p1'),
    PARTITION p2 VALUES (2)  WITH (tablename='partition_with_name_list_p2'),
    PARTITION p3 VALUES (3)  WITH (tablename='partition_with_name_list_p3'),
    PARTITION p4 VALUES (4)  WITH (tablename='partition_with_name_list_p4')
);

分裂一个分区

您可以通过ALTER TABLE语句将一个分区划分成两个分区。分裂分区存在如下限制：

• 仅支持分裂最底层的分区，即只有包含数据的分区可以被分裂。

• 分裂分区语句中指定的分裂值会被分在后一个分区中。

将2017年1月的分区分裂成两个分区，一个分区包含1月1号至15号，第二个分区包含1月16号至31号，分裂分区的示例语句如下：

ALTER TABLE sales SPLIT PARTITION FOR ('2017-01-01')
AT ('2017-01-16')
INTO (PARTITION jan171to15, PARTITION jan1716to31);

如果您的分区表中拥有一个默认分区，可以使用分裂默认分区的方法来增加分区。在使用INTO子句时，您需要指定当前的默认分区为第二个分区名。分裂默认分区的示例语句如下：

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

分区定义的粒度

在您使用分区表的过程中，可能会遇到分区表粒度的问题，例如按时间分区的情况下，选择按天、按周还是按月进行分区。分区表的粒度越细，每张分区表的数据就越少，分区表的数量就越多。关于分区表的数量，并没有绝对的标准，建议分区的数量控制在200以内，分区表数量过多可能会对数据库使用产生影响，例如查询优化器生成执行计划慢，VACUUM执行变慢等。

分区表查询优化

AnalyticDB PostgreSQL版支持分区表的分区裁剪功能，根据查询条件会只扫描所需的数据分区而避免扫描整个表的全部内容，提升查询性能。例如对于如下查询：

EXPLAIN 
  SELECT * FROM sales 
  WHERE year = 2008 
    AND month = 1 
    AND day = 3 
    AND region = 'usa';

查询条件在一级分区2008的二级子分区1的三级子分区usa上，查询只会扫描读取这一个三级子分区数据。如下查询计划所示，总计468个三级子分区中，只需要读取一个分区。

Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..431.00 rows=1 width=24)
  ->  Sequence  (cost=0.00..431.00 rows=1 width=24)
        ->  Partition Selector for sales (dynamic scan id: 1)  (cost=10.00..100.00 rows=25 width=4)
              Filter: year = 2008 AND month = 1 AND region = 'usa'::text
              Partitions selected:  1 (out of 468)
        ->  Dynamic Table Scan on sales (dynamic scan id: 1)  (cost=0.00..431.00 rows=1 width=24)
              Filter: year = 2008 AND month = 1 AND day = 3 AND region = 'usa'::text

查询分区表定义

您可以通过如下SQL语句查询分区表的所有分区定义信息：

SELECT 
  partitionboundary, 
  partitiontablename, 
  partitionname,
  partitionlevel, 
  partitionrank
FROM pg_partitions 
WHERE tablename='sales';

分区表维护

分区表支持多种分区管理操作，包括新增分区，删除分区，重命名分区，清空截断分区，交换分区，分裂分区等，具体信息，请参见Partitioning Large Tables。

分区表子表重命名

AnalyticDB PostgreSQL版在6.3.10.9版本之后支持对分区表子表重命名。

如果您需要将子分区表的表名从partition_with_name_list_p1改为partition_with_name_list_p1r，示例SQL如下：

ALTER TABLE partition_with_name_list_p1 RENAME TO partition_with_name_list_p1r;

常见问题

Q：分区键必须是主键吗？

A：有主键时，分区键必须是主键之一；没有主键时，分区键可以是任意列。