使用ALTER TABLE…DROP SUBPARTITION删除复合分区表的子分区-云原生数据库 PolarDB-阿里云

ALTER TABLE ... DROP SUBPARTITION 用于从复合分区表中删除指定的子分区及其数据，此操作会永久移除数据，执行前需确认目标分区已备份或不再需要。

语法介绍

ALTER TABLE table_name DROP SUBPARTITION subpartition_name;

参数说明

参数名称	参数要求	参数说明	参数示例
`table_name`	必填	包含要删除子分区的复合分区表的名称。	`customer_data`
`subpartition_name`	必填	要删除的子分区的名称。	`p_2022_asia`

注意事项

执行此命令的用户必须是该表的所有者或者是高权限用户。
不支持删除表中的最后一个子分区。
DROP SUBPARTITION会获取AccessExclusiveLock（表级排他锁），阻塞该表的所有 DML 和大部分 DDL 操作。建议在业务低峰期执行，并预留足够的时间窗口。

使用示例

本示例演示如何在一个按注册年份（RANGE）和用户所在地区（LIST）进行复合分区的客户表中，删除2022年度亚洲地区的用户数据子分区，以释放存储空间。

环境准备

本步骤将创建一个复合分区表 customer_data，该表按注册年份进行范围分区，再按用户所在地区进行列表子分区。

-- 创建复合分区表
CREATE TABLE customer_data (
    customer_id  INT NOT NULL,
    region       VARCHAR2(10),
    reg_date     DATE
)
PARTITION BY RANGE (reg_date)
SUBPARTITION BY LIST (region)
(
    PARTITION p_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION p_2022_asia    VALUES ('Asia'),
        SUBPARTITION p_2022_europe  VALUES ('Europe')
    ),
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION p_2023_asia    VALUES ('Asia'),
        SUBPARTITION p_2023_europe  VALUES ('Europe')
    )
);

-- 插入数据
INSERT INTO customer_data VALUES (101, 'Asia', TO_DATE('2022-06-10', 'YYYY-MM-DD'));
INSERT INTO customer_data VALUES (102, 'Europe', TO_DATE('2022-07-20', 'YYYY-MM-DD'));
INSERT INTO customer_data VALUES (103, 'Asia', TO_DATE('2023-06-10', 'YYYY-MM-DD'));
INSERT INTO customer_data VALUES (104, 'Europe', TO_DATE('2023-07-20', 'YYYY-MM-DD'));

前置检查

在执行删除操作前，确认数据已备份，并检查待删除的子分区结构。

-- 重要：执行删除操作前，请确保已对相关数据进行备份。

-- 检查子分区结构，确认待删除的子分区 p_2022_asia 存在
SELECT SUBPARTITION_NAME 
FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'CUSTOMER_DATA';

执行操作

执行 DROP SUBPARTITION 命令删除 p_2022_asia 子分区。

ALTER TABLE customer_data DROP SUBPARTITION p_2022_asia;

结果验证

验证子分区和其对应的数据均已被删除。

-- 结构验证：确认子分区已被删除
SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'CUSTOMER_DATA';
-- 查询结果中已不包含 p_2022_asia

-- 数据验证：确认子分区内的数据已被删除
SELECT COUNT(*) FROM customer_data WHERE customer_id = 101;
-- 查询结果为 0

更新表统计信息
删除分区后，建议立即更新表的统计信息，以确保查询优化器能够生成准确的执行计划。
```
ANALYZE CUSTOMER_DATA;
```

常见错误

Q1：为何出现ORA-00942: table or view does not exist 错误？

此错误通常由两个原因导致：1. 当前用户缺少对目标表的 ALTER 权限。2. 表名或 Schema名称拼写错误。请检查权限配置和对象名称的准确性。

Q2：为何出现ORA-14006: invalid partition name 错误？
这表明指定的子分区名称不存在或拼写错误。可查询 USER_TAB_SUBPARTITIONS视图，以核实目标子分区的正确名称。注意，若名称未用双引号括起，数据库通常会将其转换为大写字母进行匹配。

Q3：为何出现ORA-01031: insufficient privileges错误？
这是因为执行该命令的用户不具备目标表的 ALTER 权限，需要联系数据库管理员授予相应权限。

Q4：执行DROP SUBPARTITION后，数据可以恢复吗？
不可以，因为 DROP SUBPARTITION 是一项物理删除操作且不会进入回收站，恢复数据的唯一方法是使用执行此操作前的数据库备份进行还原。