文档首页/ 数据仓库服务 GaussDB(DWS)/ SQL语法参考/ SQL语法参考(8.3.0.x)/ DDL语法/ ALTER TABLE PARTITION
更新时间：2024-10-25 GMT+08:00
查看PDF
分享

ALTER TABLE PARTITION

功能描述

修改表分区，包括增删分区、切割分区、合成分区，以及修改分区属性等。

注意事项

  • 添加分区的名字不能与该分区表已有分区的名字相同。
  • 对于范围分区表，要添加的分区的边界值要和分区表的分区键的类型一致，且要大于分区表的最后一个分区的上边界。
  • 对于列表分区表，如果已经定义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操作，最后顺序执行其它分区维护操作。 
    1
2
3
4
5
6
7
8
9
    		 
        modify_clause  |
    rebuild_clause |
    exchange_clause  |
    row_clause  |
    merge_clause  |
    split_clause  |
    add_clause  |
    drop_clause  |
    truncate_partitioned_clause
    • modify_clause子语法用于设置分区索引是否可用。 
      1
      		 
      MODIFY PARTITION partition_name { UNUSABLE LOCAL INDEXES | REBUILD UNUSABLE LOCAL INDEXES }
    • rebuild_clause子语法用来重建分区的索引。该语法仅8.3.0.100及以上集群版本支持。 
      1
      		 
      REBUILD PARTITION partition_name [ WITHOUT UNUSABLE ]
    • exchange_clause子语法用于把普通表的数据迁移到指定的分区。 
      1
2
3
      		 
      EXCHANGE PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } 
    WITH TABLE {[ ONLY ] ordinary_table_name | ordinary_table_name * | ONLY ( ordinary_table_name )} 
    [ { WITH | WITHOUT } VALIDATION ] [ VERBOSE ]

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

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

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

    • row_clause子语法用于设置分区表的行迁移开关。 
      1
      		 
      { ENABLE | DISABLE } ROW MOVEMENT
    • merge_clause子语法用于把多个分区合并成一个分区。 
      1
      		 
      MERGE PARTITIONS { partition_name } [, ...] INTO PARTITION partition_name
      • INTO关键字前的分区称为源分区，INTO关键字后的分区称为目标分区。
      • 源分区个数不能小于2个。
      • 源分区名称不能重复。
      • 源分区不能存在unusable的索引，否则执行会报错。
      • 目标分区名只能跟最后一个源分区的名称相同，或者跟表的所有分区名都不相同。
      • 目标分区的边界是所有源分区边界的并集。
      • 对于范围分区表，所有的源分区必须是边界连续的分区。
      • 对于列表分区，如果源分区中包含DEFAULT分区，那么目标分区的边界也是DEFAULT。
    • split_clause子语法用于把一个分区切割成多个分区。

      范围分区的split_clause语法如下：

      1
      		 
      SPLIT PARTITION { partition_name | FOR ( partition_value [, ...] ) } { split_point_clause | no_split_point_clause }
      • 指定切割点split_point_clause的语法为： 
        1
        		 
        AT ( partition_value ) INTO ( PARTITION partition_name  , PARTITION partition_name  )

        切割点的大小要位于正在被切割的分区的分区键范围内，指定切割点的方式只能把一个分区切割成两个新分区。

      • 不指定切割点no_split_point_clause的语法为。 
        1
        		 
        INTO { ( partition_less_than_item [, ...] ) | ( partition_start_end_item [, ...] ) }
        • 不指定切割点的方式，partition_less_than_item指定的第一个新分区的分区键要大于正在被切割的分区的前一个分区（如果存在的话）的分区键，partition_less_than_item指定的最后一个分区的分区键要等于正在被切割的分区的分区键大小。
        • 不指定切割点的方式，partition_start_end_item指定的第一个新分区的起始点（如果存在的话）必须等于正在被切割的分区的前一个分区（如果存在的话）的分区键，partition_start_end_item指定的最后一个分区的终止点（如果存在的话）必须等于正在被切割的分区的分区键。
        • partition_less_than_item支持的分区键个数最多为4，而partition_start_end_item仅支持1个分区键，其支持的数据类型参见Partition Key。
        • 在同一语句中partition_less_than_item和partition_start_end_item两者不可同时使用；不同split语句之间没有限制。
      • 分区项partition_less_than_item的语法为： 
        1
2
        		 
        PARTITION partition_name VALUES LESS THAN ( { partition_value | MAXVALUE }  [, ...] )
      • 分区项partition_start_end_item的语法为，其约束参见START END语法描述。 
        1
2
3
4
5
6
        		 
        PARTITION partition_name {
        {START(partition_value) END (partition_value) EVERY (interval_value)} |
        {START(partition_value) END ({partition_value | MAXVALUE})} |
        {START(partition_value)} |
        {END({partition_value | MAXVALUE})}
}
      列表分区的split_clause语法如下： 
      1
      		 
      SPLIT PARTITION { partition_name | FOR ( partition_value [, ...] ) } { split_values_clause | split_no_values_clause }
      • 指定切割点的split_values_clause的语法为： 
        1
        		 
        VALUES ( { (partition_value) [, ...] } | DEFAULT } ) INTO ( PARTITION partition_name  , PARTITION partition_name  )
        • 如果源分区不是DEFAULT分区，那么切割点所指定的边界是源分区边界的一个非空真子集；如果源分区是DEFAULT分区，那么切割点所指定的边界不能和其它非DEFAULT分区的边界存在重叠。
        • 切割点的指定的边界是INTO关键字后面的第一个分区的边界，源分区边界与切割点的指定的边界的差集是第二个分区的边界。
        • 当源分区是DEFAULT分区时，第二个分区的边界还是DEFAULT。
      • 不指定切割点的split_no_values_clause的语法为： 
        1
        		 
        INTO ( list_partition_item [, ....], PARTITION partition_name )
        • 此处的list_partition_item和创建列表分区表的时候指定分区的语法一样，除了此处的分区定义中边界值不能为DEFAULT。
        • 除了最后一个分区，其他分区需要显式定义边界，定义的边界不能是DEFAULT，并且必须是源分区边界的非空真子集。最后一个分区的边界是源分区边界与其它分区边界的差集，且最后一个分区的边界为空(即差集不能为空集)。
        • 如果源分区是DEFAULT分区，则最后一个分区的边界为DEFAULT。
    • add_clause子语法用于为指定的分区表添加一个或多个分区。
      范围分区的add_clause语法如下： 
      1
      		 
      ADD { partition_less_than_item... | partition_start_end_item }
      • 使用partition_less_than_item语法时，分区表必须是范围分区表，否则执行会报错。
      • 此处partition_less_than_item和创建范围分区表的时候指定分区的语法一样。
      • 当前分区表的最后一个分区的边界为MAXVALUE，不允许添加新的分区，否则执行会报错。

      列表分区的add_clause语法如下：

      1
      		 
      ADD list_partition_item
      • 使用list_partition_item语法时，分区表必须是列表分区表，否则执行会报错
      • 此处的list_partition_item和创建列表分区表的时候指定分区的语法一样
      • 当前分区表存在DEFAULT分区时，不允许添加新的分区动作，否则执行会报错
    • drop_clause子语法用于删除分区表中的指定分区。 
      1
      		 
      DROP PARTITION  { partition_name | FOR (  partition_value [, ...] )  }
    • drop_clause子语法支持删除多个分区语法。（8.1.3.100及以上集群版本支持。） 
      1
      		 
      DROP PARTITION  { partition_name [, ... ] }
    • truncate_partitioned_clause子语法用于清理表分区的数据。 
      1
      		 
      TRUNCATE PARTITION { partition_name | FOR (  partition_value  [, ...] )  } ;

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

参数说明

  • table_name

    分区表名。

    取值范围：已存在的分区表名。

  • partition_name

    分区名。

    取值范围：已存在的分区名。

  • partition_value

    分区键值。

    通过PARTITION FOR ( partition_value [, ...] )子句指定的这一组值，可以唯一确定一个分区。

    取值范围：需要进行重命名的分区的分区键的取值范围。

  • UNUSABLE LOCAL INDEXES

    设置该分区上的所有索引不可用。

  • REBUILD UNUSABLE LOCAL INDEXES

    重建该分区上的所有索引。

  • WITHOUT UNUSABLE

    重建该分区上的索引时，忽略UNUSABLE状态的索引。该参数仅8.3.0.100及以上集群版本支持。

  • ENABLE/DISABLE ROW MOVEMENT

    行迁移开关。

    取值范围：

    • ENABLE：打开行迁移开关。
    • DISABLE：关闭行迁移开关。

    默认是关闭状态。

    • ENABLE ROW MOVEMENT开启则允许跨分区更新，但此时如果有SELECT FOR UPDATE查询该分区表并发执行，存在查询结果瞬时不一致的可能性，需要谨慎使用。
    • 如果进行UPDATE操作时，更新了元组在分区键上的值，造成了该元组所在分区发生变化，就会根据该开关给出报错信息，或者进行元组在分区间的转移。
  • ordinary_table_name

    进行迁移的普通表的名称。

    取值范围：已存在的普通表名。

  • { WITH | WITHOUT } VALIDATION

    在进行数据迁移时，是否检查普通表中的数据满足指定分区的分区键范围。

    取值范围：

    • WITH：对于普通表中的数据要检查是否满足分区的分区键范围，如果有数据不满足，则报错。
    • WITHOUT：对于普通表中的数据不检查是否满足分区的分区键范围。

    默认是WITH状态。

    由于检查比较耗时，特别是当数据量很大的情况下更甚。所以在保证当前普通表中的数据满足分区的分区键范围时，可以加上WITHOUT来指明不进行检查。

    交换的数据不符合分区条件会导致数据异常，如果该表上存在物化视图，也会导致物化视图的数据与原表不一致。

  • VERBOSE

    在VALIDATION是WITH状态时，如果检查出普通表有不满足要交换分区的分区键范围的数据，那么把这些数据插入到正确的分区，如果路由不到任何分区，再报错。

    只有在VALIDATION是WITH状态时，才可以指定VERBOSE。

  • partition_new_name

    分区的新名字。

    取值范围：字符串，要符合标识符的命名规范。

示例

  • add_clause子句用于为指定的分区表添加一个或多个分区。

    为范围分区表customer_address增加分区ca_address_sk介于700和900之间：

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

    为范围分区表customer_address增加分区: [5000, 5300), [5300, 5600), [5600, 5900), [5900, 6000)：

    1
    		 
    ALTER TABLE customer_address_SE ADD PARTITION p6 START(5000) END(6000) EVERY(300);

    为范围分区表customer_address增加MAXVALUE分区p6：

    1
    		 
    ALTER TABLE customer_address ADD PARTITION p6 END(MAXVALUE);

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

    1
    		 
    ALTER TABLE data_list ADD PARTITION P6 VALUES (202302,202303);
  • modify_clause子句用于设置分区索引是否可用。

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

    1
2
3
4
5
6
7
8
9
    		 
    CREATE INDEX customer_address_index ON customer_address(ca_address_id) LOCAL
(
        PARTITION P1_index,
        PARTITION P2_index,
        PARTITION P3_index,
        PARTITION P4_index,
        PARTITION P5_index,
        PARTITION P6_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;
  • split_clause子句用于把一个分区切割成多个分区。

    将范围分区表customer_address的P6分区以1200为分割点切分：

    1
    		 
    ALTER TABLE customer_address SPLIT PARTITION P6 AT(1200) INTO (PARTITION P6a,PARTITION P6b);

    将范围分区表customer_address中200所在的分区分割成多个分区：

    1
    		 
    ALTER TABLE customer_address SPLIT PARTITION FOR(200) INTO(PARTITION p_part START(100) END(300) EVERY(50));

    将列表分区表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）的操作。创建分区表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')
);

    添加数据到分区表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');

    查询分区表student_grade的math分区记录：

    1
2
3
4
5
6
    		 
    SELECT * FROM student_grade PARTITION (math);
 stu_name |  stu_no  | grade | subject
----------+----------+-------+---------
 Jeck     | 20220103 |    60 | math
 Sam      | 20220105 |    72 | math
(2 rows)

    创建一个与分区表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)
);

    添加了数据到表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');

    将普通表math_grade数据迁移到分区表student_grade 中分区（math）：

    1
    		 
    ALTER TABLE student_grade EXCHANGE PARTITION (math) WITH TABLE math_grade;

    对分区表student_grade的查询表明表math_grade中的数据已和分区math中的数据交换：

    1
2
3
4
5
6
7
8
    		 
    SELECT * FROM student_grade PARTITION (math);
 stu_name |  stu_no  | grade | subject
----------+----------+-------+---------
 Anna     | 20220108 |    56 | math
 Jeck     | 20220103 |    60 | math
 Ann      | 20220101 |    75 | math
 Jann     | 20220107 |    82 | math
(4 rows)

    对表math_grade的查询显示了之前存储在分区math中的记录已被移动到表student_grade中：

    1
2
3
4
5
6
    		 
    SELECT * FROM math_grade；
 stu_name |  stu_no  | grade | subject
----------+----------+-------+---------
 Jeck     | 20220103 |    60 | math
 Sam      | 20220105 |    72 | math
(2 rows)
  • truncate_partitioned_clause子语法用于清理表分区的数据。

    清空表student_grade分区p1：

    1
    		 
    ALTER TABLE student_grade TRUNCATE PARTITION p1;
  • row_clause子句用于设置分区表的行迁移开关。

    打开分区表customer_address的迁移开关：

    1
    		 
    ALTER TABLE customer_address ENABLE ROW MOVEMENT;
  • merge_clause子句用于把多个分区合并成一个分区。

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

    1
    		 
    ALTER TABLE customer_address MERGE PARTITIONS P2, P3 INTO PARTITION P_M;
  • drop_clause子句用于删除分区表中的指定分区。

    删除分区表customer_address的分区P6：

    1
    		 
    ALTER TABLE customer_address DROP PARTITION P6;

    删除分区表customer_address的多个分区P3，P4，P5：

    1
    		 
    ALTER TABLE customer_address DROP PARTITION P3, P4, P5;

相关链接

CREATE TABLE PARTITION，DROP TABLE

父主题： DDL语法