ALTER TABLE PARTITION

功能描述

本章节介绍修改表分区的语法功能，包括增删分区、切割分区、合成分区、从普通表数据迁移到分区表，以及修改分区属性等。

逻辑上的一张表根据某种方案分成几张物理块进行存储，这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。

常见的分区策略都是按照某一列或者某几列定义一些数据分布范围，然后每个分区承载一个范围的数据，这些列称之为分区键。

目前GaussDB(DWS)行存表、列存表支持的分区类型包括范围分区和列表分区。

• 范围分区（Range Partitioning）

  根据表的一列或者多列，将要插入表的记录分为若干个范围，这些范围在不同的分区里没有重叠。为每个范围创建一个分区，用来存储相应的数据。

  • 核心特征：数据按连续区间分布，常用于有序且按范围检索的场景。
  • 适用场景：分区键具有线性增长或连续区间特征，数据查询常围绕 “某一范围” 展开，且新数据会自然落入新的区间。例如按时间范围进行分区的订单数据，按数值范围分区的用户等级数据等。
  • 范围分区策略是指记录插入分区的方式，根据分区键值将记录映射到已创建的某个分区上，如果可以映射到已创建的某一分区上，则把记录插入到对应的分区上，否则给出报错和提示信息，这是最常用的分区策略。目前范围分区仅支持范围分区策略。
• 列表分区（List Partitioning）

  根据表的一列，将要插入表的记录通过每一个分区中出现的键值划分到对应的分区中，这些键值在不同的分区里没有重叠。为每组键值创建一个分区，用来存储相应的数据。列表分区仅8.1.3及以上集群版本支持。

  • 核心特征：数据按离散的枚举值划分，常用于固定分类或高频过滤的维度。
  • 适用场景：分区键值是固定且离散的类别，数据查询常围绕 “某一类别” 展开，键值不会无规律新增。例如按省份分区的用户信息表，每个省份对应一个分区。
  • 列表分区策略是根据分区键值将记录映射到已创建的某个分区上，如果可以映射到已创建的某一分区上，则把记录插入到对应的分区上，否则给出报错和提示信息。

注意事项

• 添加分区的名称不能与该分区表已有分区的名称相同。
• 对于范围分区表，要添加的分区的边界值要和分区表的分区键的类型一致，且要大于分区表的最后一个分区的上边界。
• 对于列表分区表，如果已经定义DEFAULT分区，则不能添加新分区。
• 若文档中未特殊注明，则表明范围分区表和列存分区的语法使用相同。
• 如果目标分区表中已有分区数达到了最大值（32767），则不能继续添加分区。

• 当分区表只有一个分区时，不能删除该分区。
• 删除分区（DROP PARTITION）时会连同分区内数据一起删除。
• 选择分区使用PARTITION FOR()，括号里指定值个数应该与定义分区时使用的列个数相同，并且一一对应。
• Value分区表不支持相应的Alter Partition操作。
• OBS冷热表对于move，exchange，merge，split操作，不支持指定分区表的表空间为OBS表空间；执行ALTER语法时，需保持分区数据冷热属性不变（即冷分区操作后为冷分区，热分区操作后为热分区），不支持将冷分区数据切至本地表空间；对于冷分区仅支持默认表空间；merge操作不支持将冷分区与热分区进行合并，exchange操作不支持冷分区交换。

警告：

• 避免在业务高峰期执行ALTER TABLE/ALTER TABLE PARTITION（增删改查、DROP PARTITION）、TRUNCATE操作，避免有长SQL阻塞ALTER、TRUNCATE操作或SQL业务被ALTER、TRUNCATE阻塞。
• 更多开发设计规范参见总体开发设计规范。

语法格式

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
    action [, ... ];

其中action统指如下分区维护子语法。当存在多个分区维护子句时，保证了分区的连续性，无论这些子句的排序如何，GaussDB(DWS)总会先执行DROP PARTITION再执行ADD PARTITION操作，最后顺序执行其它分区维护操作。

• modify_clause子语法：设置分区索引是否可用
• rebuild_clause子语法：重建分区索引
• exchange_clause子语法：把普通表的数据迁移到指定的分区
• row_clause子语法：设置分区表的行迁移开关
• merge_clause子语法：把多个分区合并成一个分区
• split_clause子语法：把一个分区切割成多个分区
• add_clause子语法：为指定的分区表添加一个或多个分区
• drop_clause子语法：删除分区表中的指定分区或多个分区
• truncate_partitioned_clause子语法：清理表分区的数据

ALTER TABLE PARTITION主句参数说明

表1 ALTER TABLE PARTITION主句参数说明

参数	描述	取值范围
table_name	需要修改的分区表的名称。	已存在的分区表名。
action [, ... ]	action统指分区维护子语法。当存在多个分区维护子句时，保证了分区的连续性，无论这些子句的排序如何，GaussDB(DWS)总会先执行DROP PARTITION再执行ADD PARTITION操作，最后顺序执行其它分区维护操作。	参见以下各个子语法使用。
partition_name	需要修改的分区名。	已存在的分区名。
partition_value	分区键值。 通过PARTITION FOR ( partition_value [, ...] )子句指定的这一组值，可以唯一确定一个分区。	需要进行重命名分区的分区键的取值范围。

modify_clause子语法：设置分区索引是否可用

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
MODIFY PARTITION partition_name { UNUSABLE LOCAL INDEXES | REBUILD UNUSABLE LOCAL INDEXES }

rebuild_clause子语法：重建分区索引

该语法仅8.3.0.100及以上集群版本支持。

exchange_clause子语法：把普通表的数据迁移到指定的分区

1
2
3
4

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
EXCHANGE PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } 
    WITH TABLE {[ ONLY ] ordinary_table_name | ordinary_table_name * | ONLY ( ordinary_table_name )} 
    [ { WITH | WITHOUT } VALIDATION ] [ VERBOSE ]

表4 exchange_clause子语法参数说明

参数	描述	取值范围
partition_value	分区键值。 通过PARTITION FOR ( partition_value [, ...] )子句指定的这一组值，可以唯一确定一个分区。	需要进行重命名分区的分区键的取值范围。
ordinary_table_name	进行迁移的普通表的名称。	已存在的普通表名。
{ WITH | WITHOUT } VALIDATION	在进行数据迁移时，是否检查普通表中的数据满足指定分区的分区键范围。默认为WITH。 由于检查比较耗时，特别是当数据量很大的情况下更甚。所以在保证当前普通表中的数据满足分区的分区键范围时，可以加上WITHOUT来指明不进行检查。	WITH：对于普通表中的数据要检查是否满足分区的分区键范围，如果有数据不满足，则报错。 WITHOUT：对于普通表中的数据不检查是否满足分区的分区键范围。
VERBOSE	在VALIDATION是WITH状态时，如果检查出普通表有不满足要交换分区的分区键范围的数据，那么把这些数据插入到正确的分区，如果路由不到任何分区，再报错。 注意： 只有在VALIDATION是WITH状态时，才可以指定VERBOSE。	-

进行交换的普通表和分区表必须满足如下条件：

• 普通表和分区表的列数目相同，对应列的信息严格一致，包括：列名、列的数据类型、列约束、列的Collation信息、列的存储参数、列的压缩信息、已删除字段的数据类型等。
• 普通表和分区表的表压缩信息严格一致。
• 普通表和分区表的分布列信息严格一致。
• 普通表和分区表的索引个数相同，且对应索引的信息严格一致。
• 普通表和分区表的表约束个数相同，且对应表约束的信息严格一致。
• 普通表不可以是临时表和unlogged表。
• 普通表和分区表应该在同一个逻辑集群或节点组（NodeGroup）中，如果不在同一个逻辑集群或节点组，将会采用将数据插入对方表内方式来实现交换分区，这样交换分区的时间与表数据量有关，对于数据量非常大的表和分区表，交换分区将会非常耗时。
• 在线扩容重分布场景中，如果普通表和分区表正在重分布，交换分区语句有可能中断正在重分布的普通表或分区表（取决于交换分区和重分布语句是否产生锁冲突），通常重分布的普通表或分区表被中断后会重试2次，但同一个表交换分区执行过于频繁可能导致普通表或分区表多次重试重分布都失败。如果普通表重分布过程被交换分区操作打断，在重试重分布时，数据已经被替换为原分区表中的数据，会重新进行全量重分布。
• 如果行存分区表中最后一个有效字段后的其他字段全部被删除，在不考虑这些删除字段的情况下，分区表与普通表字段信息一致时，分区表和普通表可以进行交换。
• 列存普通表和列存分区表的表级参数colversion必须一致：禁止colversion2.0与colversion1.0执行交换分区操作。

完成交换后，普通表和分区表的数据被置换，同时普通表和分区表的表空间信息被置换。此时，普通表和分区表的统计信息变得不可靠，需要对普通表和分区表重新执行analyze。

row_clause子语法：设置分区表的行迁移开关

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
{ ENABLE | DISABLE } ROW MOVEMENT

表5 row_clause子语法参数说明

参数	描述	取值范围
ENABLE/DISABLE ROW MOVEMENT	行迁移开关，当更新表中某行分区键值时，如果新值属于其他分区，则会根据开关给出报错信息或者将该行数据迁移到对应的分区。 ENABLE：打开行迁移功能，适用于频繁更新分区键的场景。 DISABLE：关闭行迁移功能，适用于严格禁止数据跨分区更新的场景，更新分区键会报错。 默认为DISABLE，关闭。 注意： 开启ROW MOVEMENT则允许跨分区更新，但此时如果有SELECT FOR UPDATE查询该分区表并发执行，存在查询结果瞬时不一致的可能性，需要谨慎使用。	举例，某分区表的日期列（分区键），按季度进行分区，分为分区p_2023q1和p_2023q2，某一行的“2023-02-15”原属于第一季度的分区p_2023q1，当更新该值为“2023-05-15”后，对应的这行数据，会根据ROW MOVEMENT开关是否打开而决定是否行数据迁移： ENABLE：打开行迁移功能，则该数据迁移到新分区p_2023q2。 DISABLE：关闭行迁移功能，则系统会报错。 详细示例参见示例：开启和关闭行迁移功能ROW MOVEMENT。

merge_clause子语法：把多个分区合并成一个分区

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
MERGE PARTITIONS { partition_name } [, ...] INTO PARTITION partition_name  

• INTO关键字前的分区称为源分区，INTO关键字后的分区称为目标分区。
• 源分区个数不能小于2个。
• 源分区名称不能重复。
• 源分区不能存在unusable的索引，否则执行会报错。
• 目标分区名只能跟最后一个源分区的名称相同，或者跟表的所有分区名都不相同。
• 目标分区的边界是所有源分区边界的并集。
• 对于范围分区表，所有的源分区必须是边界连续的分区。
• 对于列表分区，如果源分区中包含DEFAULT分区，那么目标分区的边界也是DEFAULT。

split_clause子语法：把一个分区切割成多个分区

范围分区的split_clause语法如下：

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
SPLIT PARTITION { partition_name | FOR ( partition_value [, ...] ) } { split_point_clause | no_split_point_clause }

add_clause子语法：为指定的分区表添加一个或多个分区

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
ADD { partition_less_than_item... | partition_start_end_item }

注意：

• 使用partition_less_than_item语法时，分区表必须是范围分区表，否则执行会报错。
• 此处partition_less_than_item和创建范围分区表的时候指定分区的语法一样。
• 当前分区表的最后一个分区的边界为MAXVALUE，不允许添加新的分区，否则执行会报错。

列表分区的add_clause语法如下：

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
ADD list_partition_item

drop_clause子语法：删除分区表中的指定分区或多个分区

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
DROP PARTITION  { partition_name | FOR (  partition_value [, ...] )  } 

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
DROP PARTITION  { partition_name [, ... ] }

truncate_partitioned_clause子语法：清理表分区的数据

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
TRUNCATE PARTITION { partition_name | FOR (  partition_value  [, ...] )  };

修改表分区名称语法

1
2

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name  )}
    RENAME PARTITION { partition_name | FOR ( partition_value [, ...] ) } TO partition_new_name;

表6 修改分区名称参数说明

参数	描述	取值范围
partition_new_name	分区的新名称。	字符串，需符合标识符命名规范。

示例：创建范围分区表customer_address

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

DROP TABLE IF EXISTS customer_address;
CREATE TABLE 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)                    
)
DISTRIBUTE BY HASH (ca_address_sk)
PARTITION BY RANGE(ca_address_sk)
(
        PARTITION P1 VALUES LESS THAN(100),
        PARTITION P2 VALUES LESS THAN(200),
        PARTITION P3 VALUES LESS THAN(300)
);

示例：创建list分区表

DROP TABLE IF EXISTS data_list;
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)
);

示例：使用modify_clause子句设置分区索引是否可用

给分区表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;

示例：使用add_clause子句为指定的分区表添加一个或多个分区

为范围分区表customer_address增加分区。

1

ALTER TABLE customer_address ADD PARTITION P5 VALUES LESS THAN (500);

为范围分区表customer_address增加分区: [500, 600), [600, 700)。

1

ALTER TABLE customer_address ADD PARTITION p6 START(500) END(700) EVERY(100);

为范围分区表customer_address增加MAXVALUE分区p7。

1

ALTER TABLE customer_address ADD PARTITION p7 END(MAXVALUE);

为列表分区表增加分区P6。

1

ALTER TABLE data_list ADD PARTITION P6 VALUES (202302,202303);

示例：使用split_clause子句将一个分区切割成多个分区

将范围分区表customer_address的P7分区以800为分割点切分。

1

ALTER TABLE customer_address SPLIT PARTITION P7 AT(800) INTO (PARTITION P6a,PARTITION P6b); 

将范围分区表customer_address中400所在的分区分割成多个分区。

1

ALTER TABLE customer_address SPLIT PARTITION FOR(400) INTO(PARTITION p_part START(300) END(500) EVERY(100));

将列表分区表data_list的分区P2分割成p2a和p2b两个分区。

1

ALTER TABLE data_list SPLIT PARTITION P2 VALUES(202210) INTO (PARTITION p2a,PARTITION p2b);

示例：使用exchange_clause子句将普通表的数据迁移到指定分区

如下示例将演示一个普通表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)

示例：使用truncate_partitioned_clause子语法清理表分区的数据

清空表customer_address分区p1：

1

ALTER TABLE customer_address TRUNCATE PARTITION p1;

示例：开启和关闭行迁移功能ROW MOVEMENT

在更新某行的分区键为新值且新值属于其他分区时，如果打开ROW MOVEMENT，则该行数据对应迁移到新的分区中。

频繁更新分区键时，ENABLE ROW MOVEMENT 可能导致额外I/O开销，需评估业务需求。

创建按日期范围分区的表，并启用行迁移。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

DROP TABLE IF EXISTS sale_data; 
CREATE TABLE  sales_data (
    sale_id  INT,
    product_name VARCHAR(100),
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2023q1 VALUES LESS THAN ('2023-04-01'),
    PARTITION p_2023q2 VALUES LESS THAN ('2023-07-01'),
    PARTITION p_2023q3 VALUES LESS THAN ('2023-10-01'),
    PARTITION p_2023q4 VALUES LESS THAN ('2024-01-01')
)
ENABLE ROW MOVEMENT;

插入测试数据。

1
2
3
4

INSERT INTO sales_data (sale_id, product_name, sale_date, amount)
VALUES 
(1, 'Product A', '2023-02-15', 1000.00),  -- 属于 p_2023q1
(2, 'Product B', '2023-05-20', 1500.00);  -- 属于 p_2023q2

查询各分区数据分布。

1

SELECT tableoid::regclass AS partition, * FROM sales_data ORDER BY sale_id;

尝试更新分区键值（从 p_2023q1 移动到 p_2023q2）。

1

UPDATE sales_data SET sale_date = '2023-05-01'  WHERE sale_id = 1;

再次查询各分区数据分布，确认行已移动。

1

SELECT tableoid::regclass AS partition, *  FROM sales_data  ORDER BY sale_id;

关闭行迁移功能。

1

ALTER TABLE sales_data DISABLE ROW MOVEMENT;

再次修改分区键（从 p_2023q2 移动回到 p_2023q2）。

UPDATE sales_data SET sale_date = '2023-02-15'  WHERE sale_id = 1;

回显提示，行迁移关闭，不允许更新分区键。

示例：使用merge_clause子句把多个分区合并成一个分区

将范围分区表customer_address的P2，P3两个分区合并为一个分区。

1

ALTER TABLE customer_address MERGE PARTITIONS P2, P3 INTO PARTITION P_M; 

示例：使用drop_clause子句删除分区表中的指定分区

删除分区表customer_address的多个分区P6a，P6b。

1

ALTER TABLE customer_address DROP PARTITION P6a, P6b;

相关链接

CREATE TABLE PARTITION，DROP TABLE

常见问题

• 使用实践：创建和管理GaussDB(DWS)分区表
• 使用实践：案例：改建分区表
• 分区表插入数据报错：inserted partition key does not map to any table partition
• 范围分区表添加新分区报错upper boundary of adding partition MUST overtop last existing partition