文档首页> 数据仓库服务 GaussDB(DWS)> 快速入门> 数据库使用入门> 创建和管理分区表
更新时间：2023-12-01 GMT+08:00
查看PDF

创建和管理分区表

背景信息

GaussDB(DWS)数据库支持的分区表为范围分区表和列表分区表。

范围分区表：将数据基于范围映射到每一个分区，这个范围是由创建分区表时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期，例如将销售数据按照月份进行分区。

列表分区表：将数据映根据分区键值映射到分区上，这些键值在不同的分区里没有重叠。为每组键值创建一个分区，用来存储相应的数据。列表分区（List Partitioning）仅8.1.3及以上集群版本支持。

分区表和普通表相比具有以下优点：

  • 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
  • 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
  • 方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。
  • 均衡I/O：可以把不同的分区映射到不同的磁盘以平衡I/O，改善整个系统性能。

普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分区表。

操作步骤

  • 按照以下方式对范围分区表进行操作。
    • 创建范围分区表 
       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
      		 
      CREATE TABLE tpcds.customer_address
(
    ca_address_sk       integer                  NOT NULL   ,
    ca_address_id       character(16)            NOT NULL   ,
    ca_street_number    character(10)                       ,
    ca_street_name      character varying(60)               ,
    ca_street_type      character(15)                       ,
    ca_suite_number     character(10)                       ,
    ca_city             character varying(60)               ,
    ca_county           character varying(30)               ,
    ca_state            character(2)                        ,
    ca_zip              character(10)                       ,
    ca_country           character varying(20)               ,
    ca_gmt_offset       numeric(5,2)                        ,
    ca_location_type    character(20)
)
DISTRIBUTE BY HASH (ca_address_sk)
PARTITION BY RANGE (ca_address_sk)
(
        PARTITION P1 VALUES LESS THAN(5000),
        PARTITION P2 VALUES LESS THAN(10000),
        PARTITION P3 VALUES LESS THAN(15000),
        PARTITION P4 VALUES LESS THAN(20000),
        PARTITION P5 VALUES LESS THAN(25000),
        PARTITION P6 VALUES LESS THAN(30000),
        PARTITION P7 VALUES LESS THAN(40000),
        PARTITION P8 VALUES LESS THAN(MAXVALUE)
)
ENABLE ROW MOVEMENT;

      当结果显示为如下信息，则表示创建成功。

      1
      		 
      CREATE TABLE

      创建列存分区表的数量建议不超过1000个。

    • 插入数据

      将表tpcds.customer_address的数据插入到表tpcds.web_returns_p2中。

      例如在数据库中创建了一个表tpcds.customer_address的备份表tpcds.web_returns_p2，现在需要将表tpcds.customer_address中的数据插入到表tpcds.web_returns_p2中，则可以执行如下命令。 
       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
31
32
      		 
      CREATE TABLE tpcds.web_returns_p2
(
    ca_address_sk       integer                  NOT NULL   ,
    ca_address_id       character(16)            NOT NULL   ,
    ca_street_number    character(10)                       ,
    ca_street_name      character varying(60)               ,
    ca_street_type      character(15)                       ,
    ca_suite_number     character(10)                       ,
    ca_city             character varying(60)               ,
    ca_county           character varying(30)               ,
    ca_state            character(2)                        ,
    ca_zip              character(10)                       ,
    ca_country           character varying(20)               ,
    ca_gmt_offset       numeric(5,2)                        ,
    ca_location_type    character(20)
)
DISTRIBUTE BY HASH (ca_address_sk)
PARTITION BY RANGE (ca_address_sk)
(
        PARTITION P1 VALUES LESS THAN(5000),
        PARTITION P2 VALUES LESS THAN(10000),
        PARTITION P3 VALUES LESS THAN(15000),
        PARTITION P4 VALUES LESS THAN(20000),
        PARTITION P5 VALUES LESS THAN(25000),
        PARTITION P6 VALUES LESS THAN(30000),
        PARTITION P7 VALUES LESS THAN(40000),
        PARTITION P8 VALUES LESS THAN(MAXVALUE)
)
ENABLE ROW MOVEMENT;
CREATE TABLE
INSERT INTO tpcds.web_returns_p2 SELECT * FROM tpcds.customer_address;
INSERT 0 0

      分区表不显示指定则默认不开启行迁移开关ROW MOVEMENT ，此时不允许跨分区更新。ENABLE ROW MOVEMENT开启则允许跨分区更新，但此时如果有SELECT FOR UPDATE查询该分区表并发执行，存在查询结果瞬时不一致的可能性，需要谨慎使用。

    • 修改分区表行迁移属性 
      1
      		 
      ALTER TABLE tpcds.web_returns_p2 DISABLE ROW MOVEMENT;
    • 删除分区
      删除分区P8。 
      1
      		 
      ALTER TABLE tpcds.web_returns_p2 DROP PARTITION P8;
    • 增加分区

      增加分区P8，范围为 40000<= P8<=MAXVALUE。

      1
      		 
      ALTER TABLE tpcds.web_returns_p2 ADD PARTITION P8 VALUES LESS THAN (MAXVALUE);
    • 重命名分区
      • 重命名分区P8为P_9。 
        1
        		 
        ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION P8 TO P_9;
      • 重命名分区P_9为P8。 
        1
        		 
        ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION FOR (40000) TO P8;
    • 查询分区
      查询分区P7。 
      1
2
      		 
      SELECT * FROM tpcds.web_returns_p2 PARTITION (P7);
SELECT * FROM tpcds.web_returns_p2 PARTITION FOR (35888);
    • 查看分区表信息，可使用系统表dba_tab_partitions。 
      1
      		 
      SELECT * FROM dba_tab_partitions WHERE table_name='customer_address';
    • 删除分区表 
      1
      		 
      DROP TABLE tpcds.web_returns_p2;
  • 按照以下方式对列表分区表进行操作。
    • 创建列表分区表 
       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
      		 
      CREATE TABLE data_list
(
    id int,
    time int, 
    sarlay decimal(12,2)
)
PARTITION BY LIST (time)
(
        PARTITION P1 VALUES (202209),
        PARTITION P2 VALUES (202210,202208),
        PARTITION P3 VALUES (202211),
        PARTITION P4 VALUES (202212),
        PARTITION P5 VALUES (202301) 
);

      当结果显示为如下信息，则表示创建成功。

      1
      		 
      CREATE TABLE
    • 插入数据 
      1
      		 
      INSERT INTO data_list VALUES (1,202209,10000),(2,202210,20000),(3,202211,30000),(4,202212,40000),(5,202301,50000),(6,202301,60000);
    • 增加分区 
      1
      		 
      ALTER TABLE data_list ADD PARTITION P6 VALUES (202302,202303);
    • 分割分区 
      1
      		 
      ALTER TABLE data_list SPLIT PARTITION P2 VALUES(202210) INTO (PARTITION p2a,PARTITION p2b);
    • 合并分区 
      1
      		 
      ALTER TABLE data_list MERGE PARTITIONS p2a,p2b INTO PARTITION P2;
    • 重命名分区
      • 重命名分区P4为P_5。 
        1
        		 
        ALTER TABLE data_list RENAME PARTITION P4 TO P_5;
      • 重命名分区P_5为P4。 
        1
        		 
        ALTER TABLE data_list RENAME PARTITION FOR (202212) TO P4;
    • 删除分区
      删除分区P1。 
      1
      		 
      ALTER TABLE data_list DROP PARTITION P1;
    • 查询分区
      查询分区P5。 
      1
2
      		 
      SELECT * FROM data_list PARTITION (P5);
SELECT * FROM data_list PARTITION FOR (202301);
    • 查看分区表信息，可使用系统表dba_tab_partitions。 
      1
      		 
      SELECT * FROM dba_tab_partitions where table_name='data_list';
    • 删除分区表 
      1
      		 
      DROP TABLE data_list;
父主题： 数据库使用入门