Oracle 11g下5000万记录表批量删除高效方案选型咨询

三个方案的优先级排序 #

首先直接排除方案一：3000万条全量删除会生成超长事务，占用的UNDO表空间会非常大，一旦执行过程中出现异常，回滚时间甚至会超过执行时间，同时会长时间锁表，完全不符合降低停机影响的要求。你写的并行提示里的first_rows完全是错误用法，这个提示是为返回少量结果的查询优化的，用于批量DML反而会严重拖慢性能。

方案二和方案三都属于分批删除的合理方向，优先推荐方案二：

• 方案三每批处理300万条记录，事务还是过大，依然存在UNDO占用过高、长锁的风险，和你要规避段空间问题的需求不符
• 方案二每批30万条的粒度非常合适，执行完一批就可以提交释放UNDO资源，也不会产生长时间的表锁，对系统的冲击最小

并行提示的使用说明 #

三个方案都可以加并行提示，但要注意两个前提：

1. 执行删除前先开启会话级并行DML，否则提示不会生效：

ALTER SESSION ENABLE PARALLEL DML;

2. 删掉没用的first_rows提示，并行度建议设置为4~8即可，不要设置过高打满服务器CPU和IO：
   优化后的参考语句（以方案二为例）：

DELETE /*+ parallel(TABLE_A, 4) */ FROM TABLE_A WHERE COLUMN_A IN (SELECT /*+ parallel(TABLE_B,4) */ COLUMN_A FROM TABLE_B WHERE QUALIFIER BETWEEN 1 AND 300000);

注意分批删除每批数据量不大，并行的收益不会特别高，如果服务器资源紧张可以不用加，避免影响其他业务。

更优方案推荐 #

你当前场景需要删除超过表总数据量的60%，比保留的数据多，最优选择是用CTAS方式重建表，效率比分批删除高3~10倍，还能直接解决段空间高水位线的问题，不需要后续做表收缩：
操作步骤参考：

1. 用直接路径读取创建保留数据的新表，NOLOGGING可以大幅减少REDO生成：

ALTER SESSION ENABLE PARALLEL DML;
CREATE TABLE TABLE_A_NEW PARALLEL 4 NOLOGGING AS SELECT * FROM TABLE_A WHERE COLUMN_A NOT IN (SELECT COLUMN_A FROM TABLE_B);

2. 按照原表的配置，给新表重建主键、索引、触发器、权限、注释等所有对象，确保和原表完全一致
3. 切换表名，完成替换：

ALTER TABLE TABLE_A RENAME TO TABLE_A_OLD;
ALTER TABLE TABLE_A_NEW RENAME TO TABLE_A;

4. 验证业务正常后可以删除原表释放空间，或者保留一段时间作为备份

注意事项 #

• 所有操作前必须做全量数据备份，避免操作失误导致数据丢失
• 执行前先在测试环境模拟一次操作，估算总耗时，确保在维护窗口内可以完成
• 分批删除时每批执行完可以等待几秒再执行下一批，给IO和UNDO资源预留缓冲时间
• CTAS方案需要提前确认有足够的空闲表空间存储新表

内容的提问来源于stack exchange，提问作者eharibabu