文档首页/ 数据仓库服务 GaussDB(DWS)/ 开发指南/ 标准数仓开发指南(8.1.3.x)/ 创建和管理GaussDB(DWS)数据库对象/ 创建和管理GaussDB(DWS)分区表
更新时间：2024-09-24 GMT+08:00
查看PDF
分享

创建和管理GaussDB(DWS)分区表

分区表就是把逻辑上的一张表根据分区策略分成几张物理块库进行存储，这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。当进行条件查询时，系统只会扫描满足条件的分区，避免全表扫描，从而提升查询性能。

分区表的优势：

  • 改善查询性能。对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
  • 增强可用性。如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
  • 提升可维护性。对于需要周期性删除的过期历史数据，可以通过drop/truncate分区的方式快速高效处理。

支持的表分区类型

  • 范围分区（Range Partitioning），基于一个数值型范围划分数据，例如按日期或价格区间定义。
  • 列表分区（List Partitioning），基于一个值列表划分数据，例如按销售范围或产品属性定义。仅8.1.3及以上集群版本支持。

分区策略选择

当表有以下特征时，可以考虑使用表分区策略：

  • 数据具有明显区间性的字段。

    分区表需要根据有明显区间性字段进行表分区。比如按照日期、区域、数值等字段进行分区，时间字段是最常见的分区字段。

  • 业务查询有明显的区间范围特征。

    查询数据可落到区间范围指定的分区内，这样才能通过分区剪枝，只扫描查询需要的分区，从而提升数据扫描效率，降低数据扫描的IO开销。

  • 表数据量比较大。

    小表扫描本身耗时不大，分区表的性能收益不明显，因此只建议对大表采取分区策略。列存储模式下因为每个列是单独的文件存储，且最小的存储单元CU可存储6w行数据，因此对于列存分区表，建议每个分区的数据不小于DN个数*6w。

创建范围(range)分区表

示例：创建一个按wr_returned_date_sk范围分区的表web_returns_p1。 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
CREATE TABLE web_returns_p1
(
    wr_returned_date_sk       integer,
    wr_returned_time_sk       integer,
    wr_item_sk                integer NOT NULL,
    wr_refunded_customer_sk   integer
)
WITH (orientation = column)
DISTRIBUTE BY HASH (wr_item_sk)
PARTITION BY RANGE (wr_returned_date_sk)
(
    PARTITION p2016 VALUES LESS THAN(20161231),
    PARTITION p2017 VALUES LESS THAN(20171231),
    PARTITION p2018 VALUES LESS THAN(20181231),
    PARTITION p2019 VALUES LESS THAN(20191231),
    PARTITION pxxxx VALUES LESS THAN(maxvalue)
);
对于分区间隔固定、批量创建分区的场景。可使用如下示例： 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
CREATE TABLE web_returns_p2
(
    wr_returned_date_sk       integer,
    wr_returned_time_sk       integer,
    wr_item_sk                integer NOT NULL,
    wr_refunded_customer_sk   integer
)
WITH (orientation = column)
DISTRIBUTE BY HASH (wr_item_sk)
PARTITION BY RANGE(wr_returned_date_sk)
(
    PARTITION p2016 START(20161231) END(20191231) EVERY(10000),
    PARTITION p0 END(maxvalue)
);
示例：创建一个按时间日期作为分区的表web_returns_p2，其中time作为分区键。 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
CREATE TABLE web_returns_p2
( 
   id integer,
   idle numeric,
   IO numeric,
   scope text,
   IP text,
   time timestamp
)
 WITH (TTL='7 days',PERIOD='1 day') 
PARTITION BY RANGE(time)
 ( 
   PARTITION P1 VALUES LESS THAN('2022-01-05 16:32:45'),
   PARTITION P2 VALUES LESS THAN('2022-01-06 16:56:12')
 );

创建列表(list)分区表

LIST分区表可以使用任意允许值比较的列作为分区键列。创建LIST分区表时，必须要为每一个分区声明每一个值分区。

示例：创建LIST分区表sales_info。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
CREATE TABLE sales_info
(
sale_time  timestamptz,
period     int,
city       text,
price      numeric(10,2),
remark     varchar2(100)
)
DISTRIBUTE BY HASH(sale_time)
PARTITION BY LIST (period, city)
(
PARTITION province1_202201 VALUES (('202201', 'city1'), ('202201', 'city2')),
PARTITION province2_202201 VALUES (('202201', 'city3'), ('202201', 'city4'), ('202201', 'city5')),
PARTITION rest VALUES (DEFAULT)
);

对已有的表进行分区

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
CREATE TABLE web_returns_p2 
(
     wr_returned_date_sk       integer,
     wr_returned_time_sk       integer,
     wr_item_sk                integer NOT NULL,
     wr_refunded_customer_sk   integer 
) 
WITH (orientation = column) 
DISTRIBUTE BY HASH (wr_item_sk) 
PARTITION BY RANGE(wr_returned_date_sk) 
(
     PARTITION p2016 START(20161231) END(20191231) EVERY(10000),
     PARTITION p0 END(maxvalue) 
);

1
2
3
4
5
 
INSERT INTO web_returns_p2 SELECT * FROM web_returns_p1;
DROP TABLE web_returns_p1;
ALTER TABLE web_returns_p2 RENAME TO web_returns_p1;
GRANT ALL PRIVILEGES ON web_returns_p1 TO dbadmin;
GRANT SELECT ON web_returns_p1 TO jack;

增加一个分区

使用ALTER TABLE语句为范围分区表增加一个分区。例如，为表web_returns_p1增加分区P2020。

1
 
ALTER TABLE web_returns_p1 ADD PARTITION P2020 VALUES LESS THAN (20201231);

分割一个分区

范围分区表和列表分区表分割分语法有所区别：

  • 使用ALTER TABLE语句为范围分区表分割一个分区。例如，将表web_returns_p1分区pxxxx以20201231为分割点分割为p2020和p20xx两个分区。 
    1
    		 
    ALTER TABLE web_returns_p1 SPLIT PARTITION pxxxx AT(20201231) INTO (PARTITION p2020,PARTITION p20xx);
  • 使用ALTER TABLE语句为列表分区表分割一个分区。例如，将表sales_info分区province2_202201分割为province3_202201和province4_202201两个分区。 
    1
    		 
    ALTER TABLE sales_info SPLIT PARTITION province2_202201 VALUES(('202201', 'city5')) INTO (PARTITION province3_202201,PARTITION province4_202201);

合并一个分区

使用ALTER TABLE语句为分区表合并一个分区。例如，将表web_returns_p1 分区p2016和p2017合并为一个分区p20162017。

1
 
ALTER TABLE web_returns_p1 MERGE PARTITIONS p2016,p2017 INTO PARTITION p20162017;

删除一个分区

使用ALTER TABLE语句从分区表中删除一个分区。例如，删除表web_returns_p1的分区P2020。 
1
 
ALTER TABLE web_returns_p1 DROP PARTITION P2020;

查询分区

  • 查询分区p2019。 
    1
2
    		 
    SELECT * FROM web_returns_p1 PARTITION (p2019);
SELECT * FROM web_returns_p1 PARTITION FOR (20201231);
  • 查看分区表信息，可使用系统表dba_tab_partitions。 
    1
    		 
    SELECT * FROM dba_tab_partitions where table_name='web_returns_p1';

删除分区表

使用DROP TABLE语句删除一个分区表。

1
 
DROP TABLE web_returns_p1;

相关文档