从Oracle数据库删除1亿行数据的最优方案探讨

方案1 vs 方案2分析 #

方案1优缺点 #

• 优势：分批提交，避免单次事务占用过多回滚段，即使中途失败，已完成的删除操作不会全部回滚；日志与删除操作一一对应，数据一致性有保障。
• 劣势：COMMIT_LIMIT = 100的设置过于保守，频繁提交会大幅增加数据库事务日志的写入开销，拖慢整体执行速度；通过游标遍历+逐条匹配ENTITY_ID删除，若ENTITY_ID无索引，删除效率极低。

方案2优缺点 #

• 优势：日志批量插入后一次性删除，减少提交次数，理论上能降低事务开销。
• 劣势：单次删除1000万行属于超大事务，会占用海量回滚段，极大概率触发回滚段不足、事务超时等问题，甚至导致数据库性能雪崩；最后删除时重新查询ENTITY_ID，若期间数据有变更（如新增/删除），会导致日志与实际删除数据不匹配，破坏一致性。

更优的第三种方案 #

针对1亿行跨表删除的场景，推荐批量删除+RETURNING子句批量记录日志的方案，同时可根据表结构调整策略：

基础优化方案（通用场景） #

使用DELETE ... RETURNING批量获取删除数据并写入日志，结合合理的批次大小（建议10000-100000，根据数据库回滚段配置调整）分批提交，既保证效率又避免资源耗尽：

DECLARE
    -- 定义日志记录类型
    TYPE t_log_record IS RECORD (
        SOURCE      VARCHAR2(100),
        SOURCE_ID   VARCHAR2(100),
        STATUS      VARCHAR2(200)
    );
    TYPE t_log_table IS TABLE OF t_log_record;
    v_log_entries t_log_table;
    
    -- 批次大小，可根据数据库配置调整
    v_batch_size CONSTANT NUMBER := 10000;
    v_total_deleted NUMBER := 0;
BEGIN
    LOOP
        -- 批量删除并返回需要记录日志的字段
        DELETE FROM SERVICE
        WHERE -- 填写你的删除条件，例如：CREATE_DATE < ADD_MONTHS(SYSDATE, -6)
        RETURNING SOURCE, SOURCE_ID, STATUS BULK COLLECT INTO v_log_entries
        LIMIT v_batch_size;
        
        -- 没有数据可删除时退出循环
        EXIT WHEN SQL%ROWCOUNT = 0;
        
        -- 批量插入日志
        FORALL i IN 1..v_log_entries.COUNT
            INSERT INTO DELETE_LOG_TEST (SOURCE, SOURCE_ID, STATUS, LOG_DATE)
            VALUES (v_log_entries(i).SOURCE, v_log_entries(i).SOURCE_ID, 
                    'Service DELETED, Status: ' || v_log_entries(i).STATUS, SYSDATE);
        
        v_total_deleted := v_total_deleted + SQL%ROWCOUNT;
        COMMIT; -- 每批次提交，释放回滚段资源
    END LOOP;
    
    -- 输出总删除行数
    DBMS_OUTPUT.PUT_LINE('总计删除数据行数：' || v_total_deleted);
END;
/

极端大表优化（分区表场景） #

如果待清理的表是分区表（例如按时间分区），直接使用分区交换操作是最优解：

1. 创建一张与原表结构一致的临时表；
2. 将待删除的分区与临时表交换（ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ...）；
3. 批量将临时表的数据插入日志表；
4. 直接删除临时表或清空数据。

这种方式几乎不产生 redo/undo 日志，执行速度是普通删除的数十倍，完全避免了大事务问题。

额外注意事项 #

• 执行前务必备份数据，或在测试环境验证；
• 避免在业务高峰期执行，减少对线上服务的影响；
• 若表上有触发器，建议先禁用，避免触发大量不必要的逻辑；
• 确保删除条件对应的字段有索引，大幅提升删除效率。

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