文档首页/ 数据仓库服务 DWS/ 更多文档/ 开发指南（巴黎区域）/ 创建和管理DWS数据库对象/ 创建和管理DWS分区表
更新时间：2025-10-11 GMT+08:00
查看PDF
分享

创建和管理DWS分区表

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

关于分区表维护的详细语法介绍请参见《SQL语法参考》中“ALTER TABLE PARTITION”章节。

分区表的优势：

  • 改善查询性能。对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
  • 增强可用性。如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
  • 提升可维护性。对于需要周期性删除的过期历史数据，可以通过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
 
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'))
);

对已有的表进行分区

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

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

为列表分区增加一个分区。例如，为表sales_info增加分区province3_202201。

1
 
ALTER TABLE sales_info ADD PARTITION province3_202201 VALUES (('202201', 'city6'), ('202201', 'city7'));

分割一个分区

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

  • 使用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;

设置分区索引是否可用

给分区表customer_address创建LOCAL索引student_grade_index，并指定分区的索引名称。

1
2
3
4
5
6
 
CREATE INDEX customer_address_index ON customer_address(ca_address_id) LOCAL
(
        PARTITION P1_index,
        PARTITION P2_index,
        PARTITION P3_index
);

重建分区表customer_address中分区P1上的所有索引。

1
 
ALTER TABLE customer_address MODIFY PARTITION P1 REBUILD UNUSABLE LOCAL INDEXES;

设置分区表customer_address的分区P3上的所有索引不可用。

1
 
ALTER TABLE customer_address MODIFY PARTITION P3 UNUSABLE LOCAL INDEXES;

重建分区索引

长期运行的分区表，索引可能产生碎片，重建索引，可提升查询效率，以下演示分区插入数据后重建分区索引。

  1. 创建分区表。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
    id          INT,
    sale_date   DATE,
    amount      DECIMAL(10,2)
) 
PARTITION BY RANGE (sale_date) 
(
    PARTITION p202310 VALUES LESS THAN ('2023-11-01'),
    PARTITION p202311 VALUES LESS THAN ('2023-12-01')
);

  2. 创建索引。 

    1
    		 
    CREATE INDEX idx_sale_date ON sales (sale_date)  LOCAL;

  3. 插入10月、11月分区数据。 

    1
2
3
4
5
6
7
    		 
    INSERT INTO sales PARTITION (p202310) 
VALUES 
(1, '2023-10-05', 100.50),
(2, '2023-10-10', 200.75);
INSERT INTO sales PARTITION (p202311) 
VALUES 
(3, '2023-11-15', 300.00);

  4. 重建特定分区p202310的索引。 

    1
    		 
    ALTER TABLE sales  REBUILD PARTITION p202310  WITHOUT UNUSABLE;

交换分区（EXCHANGE PARTITION）

如下示例将演示一个普通表math_grade数据迁移到分区表student_grade中分区（math）的操作。

  1. 创建分区表student_grade。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    		 
    CREATE TABLE student_grade (
        stu_name     char(5),
        stu_no       integer,
        grade        integer,
        subject      varchar(30)
)
PARTITION BY LIST(subject)
(
        PARTITION gym VALUES('gymnastics'),
        PARTITION phys VALUES('physics'),
        PARTITION history VALUES('history'),
        PARTITION math VALUES('math')
);
  2. 插入数据到分区表student_grade中。 
    1
2
3
4
5
6
7
    		 
    INSERT INTO student_grade VALUES 
        ('Ann', 20220101, 75, 'gymnastics'),
        ('Jeck', 20220103, 60, 'math'),
        ('Anna', 20220108, 56, 'history'),
        ('Jann', 20220107, 82, 'physics'),
        ('Molly', 20220104, 91, 'physics'),
        ('Sam', 20220105, 72, 'math');
  3. 查询分区表student_grade的math分区记录。 
    1
    		 
    SELECT * FROM student_grade PARTITION (math);

    查询结果如下：

     stu_name |  stu_no  | grade | subject
----------+----------+-------+---------
 Jeck     | 20220103 |    60 | math
 Sam      | 20220105 |    72 | math
(2 rows)
  4. 创建一个与分区表student_grade定义匹配的普通表math_grade。 
    1
2
3
4
5
6
7
    		 
    CREATE TABLE math_grade 
(
        stu_name     char(5),
        stu_no       integer,
        grade        integer,
        subject      varchar(30)
);
  5. 插入数据到表math_grade中。数据与分区表student_grade的math分区的分区规则一致。 
    1
2
3
4
5
    		 
    INSERT INTO math_grade VALUES 
        ('Ann', 20220101, 75, 'math'),
        ('Jeck', 20220103, 60, 'math'),
        ('Anna', 20220108, 56, 'math'),
        ('Jann', 20220107, 82, 'math');
  6. 将普通表math_grade数据迁移到分区表student_grade的分区（math）。 
    1
    		 
    ALTER TABLE student_grade EXCHANGE PARTITION (math) WITH TABLE math_grade;
  7. 查询分区表student_grade，结果显示表math_grade中的数据已和分区表student_grade的分区math中的数据交换。 
    1
    		 
    SELECT * FROM student_grade PARTITION (math);

    1
2
3
4
5
6
7
    		 
     stu_name |  stu_no  | grade | subject
----------+----------+-------+---------
 Anna     | 20220108 |    56 | math
 Jeck     | 20220103 |    60 | math
 Ann      | 20220101 |    75 | math
 Jann     | 20220107 |    82 | math
(4 rows)
  8. 查询表math_grade，显示之前存储在分区表student_grade的分区math中的数据已交换到表math_grade中。 
    1
    		 
    SELECT * FROM math_grade;

    1
2
3
4
5
    		 
     stu_name |  stu_no  | grade | subject
----------+----------+-------+---------
 Jeck     | 20220103 |    60 | math
 Sam      | 20220105 |    72 | math
(2 rows)
父主题： 创建和管理DWS数据库对象