使用ALTER TABLE…ADD PARTITION添加分区_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

当业务数据随时间或分类增长时，需要扩展分区表以容纳新数据。ALTER TABLE ... ADD PARTITION命令用于向现有的分区表添加一个或多个新的空分区，从而实现对新数据范围或类别的无缝管理，确保数据加载和查询的效率。

工作原理

ALTER TABLE ... ADD PARTITION是一项元数据操作，但其性能和并发影响主要取决于表上索引的配置。

锁行为 在执行ADD PARTITION期间，数据库会获取目标表上的ACCESS EXCLUSIVE锁。此锁会阻塞对该表的所有并发读写操作（SELECT, INSERT, UPDATE, DELETE）。锁的持续时间主要由在新分区上创建索引所需的时间决定。
索引维护
- 本地索引（LOCAL）：命令会自动在新的分区上创建对应的索引分区。如果表包含多个本地索引，系统会为每个索引创建新的分区，这是操作耗时的主要原因。
- 全局索引（GLOBAL）：全局索引的结构保持不变，无需额外维护。新分区中的数据在插入后会自动被全局索引覆盖。
操作耗时
- 无索引表：操作几乎瞬时完成，因为它只涉及修改表的元数据定义。
- 有索引表：操作耗时与在新分区上创建本地索引所需的时间成正比。

使用限制

基本定义规则
1. 分区类型一致性：新添加的分区需与表的现有分区类型（LIST或RANGE）保持一致。
2. 分区键一致性：新分区的分区规则需引用与表定义时完全相同的分区键列。
3. 分区名称唯一性：新分区的名称在表的所有分区和子分区中需唯一。
分区值限制
- MAXVALUE和DEFAULT分区限制
  - 限制：不能向已包含MAXVALUE分区的RANGE分区表或已包含DEFAULT分区的LIST分区表添加新分区。这是因为MAXVALUE和DEFAULT 分区已经覆盖了所有未明确指定的值，逻辑上不允许再添加新的分区。
  - 解决方案：使用ALTER TABLE ... SPLIT PARTITION命令分割MAXVALUE或DEFAULT分区，从而腾出空间创建新分区。
    说明
    此操作可能会移动数据，产生较大的I/O和锁开销，强烈建议在业务低峰期或维护窗口执行。
```
-- 示例：分割 MAXVALUE 分区以添加 2024 年的分区
ALTER TABLE sales SPLIT PARTITION max_partition AT (TO_DATE('2025-01-01', 'YYYY-MM-DD')) INTO (PARTITION p_2024, PARTITION max_partition);
```
- RANGE分区顺序要求 添加的新RANGE分区的VALUES LESS THAN值需高于表中所有现有分区的上限值。否则，操作将失败并报错ERROR:empty range bound specified for partition "xxx"。
- LIST分区值唯一性 添加的新LIST分区的值不能与任何现有分区中的值重复。否则，操作将失败并报错ERROR:partition "xx" would overlap partition "xxx"。
权限限制
执行ALTER TABLE ... ADD PARTITION命令需要具备表的所有者权限或为高权限账号。

操作建议

维护窗口执行：由于ADD PARTITION会获取表级排他锁，执行时会阻塞对表的DML和DDL操作。对于有本地索引的大型表，索引创建过程可能耗时较长，导致长时间的业务阻塞。建议在业务低峰期或指定的维护窗口内执行，以避免阻塞线上业务。
监控锁等待：在执行操作期间，监控数据库的锁等待情况。如果锁等待时间过长，可能需要终止操作并重新规划。
分区数量建议：虽然数据库在物理上对定义的分区数量没有上限，但从管理和性能角度出发，建议将单个表的分区总数控制在1000个以内。过多的分区会增加查询优化器的解析成本，可能导致查询性能下降。

语法参考

基本语法

ALTER TABLE table_name ADD PARTITION partition_spec;

partition_spec

-- For LIST partition
PARTITION partition_name VALUES (value_list)
  [TABLESPACE tablespace_name]
  [(subpartition_spec, ...)]

-- For RANGE partition
PARTITION partition_name VALUES LESS THAN (value_list)
  [TABLESPACE tablespace_name]
  [(subpartition_spec, ...)]

subpartition_spec

-- For LIST subpartition
SUBPARTITION subpartition_name VALUES (value_list)
  [TABLESPACE tablespace_name]

-- For RANGE subpartition
SUBPARTITION subpartition_name VALUES LESS THAN (value_list)
  [TABLESPACE tablespace_name]

参数说明

参数	说明
`table_name`	目标分区表的名称。
`partition_name`	要创建的新分区的名称。在表的所有分区和子分区中需唯一。
`VALUES (value_list)`	对于`LIST`分区，指定一个或多个文本值的列表。
`VALUES LESS THAN (value_list)`	对于`RANGE`分区，指定分区的上限值（不包含该值）。
`tablespace_name`	指定新分区或子分区所属的表空间。如果未指定，则使用表的默认表空间。
`subpartition_name`	要创建的子分区的名称。在表的所有分区和子分区中需唯一。

添加分区到LIST分区表

此操作用于为基于离散值（如国家、状态码）列表分区的表添加新的数据类别。

准备一个LIST分区表示例。以下sales表按country列进行分区。

CREATE TABLE sales_list (
    dept_no     NUMBER,
    part_no     VARCHAR2(50),
    country     VARCHAR2(20),
    sale_date   DATE,
    amount      NUMBER
)
PARTITION BY LIST(country) (
    PARTITION europe VALUES ('FRANCE', 'ITALY'),
    PARTITION asia VALUES ('INDIA', 'PAKISTAN'),
    PARTITION americas VALUES ('US', 'CANADA')
);

使用ALTER TABLE ... ADD PARTITION命令添加一个名为east_asia的新分区，用于存放'CHINA'和'KOREA'的数据。
```
ALTER TABLE sales_list ADD PARTITION east_asia VALUES ('CHINA', 'KOREA');
```
（可选）验证新分区是否已成功添加。
```
SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS WHERE table_name = 'sales_list';
```
查询结果应包含新添加的east_asia分区。

添加分区到RANGE分区表

此操作用于为基于连续值（如日期、ID）范围分区的表添加新的时间段或数值范围。

准备一个RANGE分区表示例。以下sales表按sale_date列进行分区。

CREATE TABLE sales_range (
    dept_no     NUMBER,
    part_no     VARCHAR2(50),
    country     VARCHAR2(20),
    sale_date   DATE,
    amount      NUMBER
)
PARTITION BY RANGE(sale_date) (
    PARTITION q1_2023 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION q2_2023 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
    PARTITION q3_2023 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
    PARTITION q4_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

说明

示例中的日期格式使用了TO_DATE函数以确保格式的明确性。实际使用时，字符串格式需符合数据库的NLS_DATE_FORMAT设置。

使用 ALTER TABLE ... ADD PARTITION 命令添加一个名为q1_2024的新分区。新分区的范围需高于所有现有分区的上限。
```
ALTER TABLE sales_range ADD PARTITION q1_2024 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD'));
```
（可选）验证新分区是否已成功添加。
```
SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS WHERE table_name = 'sales_range';
```
查询结果应包含新添加的q1_2024分区，且其分区位置在最后。

添加分区到复合分区表

对于复合分区表（如RANGE-LIST），ADD PARTITION允许在添加主分区的同时，定义其包含的子分区。

准备一个RANGE-LIST复合分区表示例。

CREATE TABLE composite_sales (
    sale_id     NUMBER,
    sale_date   DATE,
    region      VARCHAR2(20)
)
PARTITION BY RANGE(sale_date)
SUBPARTITION BY LIST(region) (
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) (
        SUBPARTITION p_2023_north VALUES ('NORTH'),
        SUBPARTITION p_2023_south VALUES ('SOUTH')
    )
);

添加一个名为p_2024的新主分区，并同时为其定义north和south两个子分区。

ALTER TABLE composite_sales ADD PARTITION p_2024
    VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) (
        SUBPARTITION p_2024_north VALUES ('NORTH'),
        SUBPARTITION p_2024_south VALUES ('SOUTH')
    );

（可选）验证新的子分区是否已成功创建。

SELECT partition_name, subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS WHERE table_name = 'composite_sales' AND partition_name = 'p_2024';

常见问题

为什么添加分区操作会卡住或导致业务超时？

ADD PARTITION操作需要获取表的排他锁。如果表上有本地索引，创建新索引分区的过程会延长锁的持有时间，从而阻塞其他所有对该表的访问。对于大型表或索引复杂的表，此过程可能需要数分钟甚至更长时间。

解决方案：

在业务低峰期执行此操作。
在执行前，评估在空表上创建索引所需的时间，以预估锁定时长。
考虑在添加分区前先禁用部分非关键索引，添加分区后再重建索引，但这会增加操作的复杂性。

尝试添加分区时收到ERROR:partition "xxx" would overlap partition "xxx"错误怎么办？

当前错误表示您尝试添加的LIST分区中包含的一个或多个值，已经存在于现有分区中。LIST分区的规则要求每个分区值在整个表中需是唯一的，不能重复分配给多个分区。

解决方案

检查重复值：确认您ADD PARTITION命令中的值列表。然后，查询数据表检查这些值具体属于哪个现有分区。
修正命令：从您的ADD PARTITION命令中移除与现有分区重复的值。确保新分区只包含全新的、未被分配的值。

示例

如果分区europe已包含'FRANCE'，那么执行ALTER TABLE table_name ADD PARTITION new_region VALUES ('FRANCE', 'SPAIN');就会触发此错误。您应将其修改为ALTER TABLE table_name ADD PARTITION new_region VALUES ('SPAIN');。

尝试添加分区时收到Specified lower bound xxx is greater than or equal to upper bound xxx.错误怎么办？

当前错误表示您尝试添加的RANGE分区的上限值不高于当前最高分区的上限值。

解决方案

查询当前最高分区边界：执行查询以找出当前表中最后一个分区的VALUES LESS THAN值。
调整新分区边界：确保您ADD PARTITION命令中VALUES LESS THAN的值大于查询到的最高边界值。您不能在现有分区之间或之前插入新分区。

示例

如果最后一个分区是PARTITION q4_2023 VALUES LESS THAN ('2024-01-01')，那么任何VALUES LESS THAN小于或等于'2024-01-01'的ADD PARTITION操作都会失败。您需使用一个更大的值，例如ALTER TABLE table_name ADD PARTITION q1_2024 VALUES LESS THAN ('2024-04-01');。