Oracle大表批量删除优化咨询：1亿条过期取消订阅数据分批方案优化

问题背景 #

需删除subs表中约1亿条满足以下条件的数据（总数据量5亿）：

• 关联Sub_status表状态为cancel
• 数据生成时间超过2个月（sub.date <= 当前日期-2个月）
  现有单次批量删除500万条的代码，以下是针对性优化建议及改进后实现：

原代码 #

DECLARE
    rows_deleted   NUMBER := 0;
    max_rows_to_delete NUMBER := 5000000;
    retention_date DATE := trunc(sysdate) - INTERVAL '2' MONTH;
BEGIN
    DELETE FROM subs
    WHERE
        EXISTS (
            SELECT
                1
            FROM
                Sub_status ss
            WHERE
                    subs.id = ss.id
                AND ss.status= 'cancel'
        )
        AND subs.date <= retention_date
        AND ROWNUM <= max_rows_to_delete;

    rows_deleted := SQL%rowcount;

    -- Log number of rows deleted
    dbms_output.put_line('Deleted ' || rows_deleted || ' rows');

    COMMIT; -- Commit changes after successful completion
EXCEPTION
    WHEN OTHERS THEN
        -- Handle exceptions or errors if necessary
        ROLLBACK; -- Rollback the transaction if an error occurs
        dbms_output.put_line('Error: ' || sqlerrm);
END;

优化建议及改进代码 #

1. 核心优化方向 #

• 循环自动执行：替代手动单次触发，直到无符合条件的数据可删
• 范围分页替代ROWNUM：避免重复/遗漏删除，基于主键范围分批更稳定
• 索引前置优化：确保关联、过滤字段有高效索引，避免全表扫描
• 事务与日志管控：降低回滚段压力，改用持久化表记录删除日志
• 并行删除（可选）：利用多CPU资源加速删除（需结合系统负载调整）

2. 改进后代码 #

DECLARE
    v_rows_deleted   NUMBER := 0;
    v_batch_size     NUMBER := 5000000; -- 可根据系统负载调整，比如100万
    v_retention_date DATE := TRUNC(SYSDATE) - INTERVAL '2' MONTH;
    v_last_id        NUMBER := 0;
    v_total_deleted  NUMBER := 0;
BEGIN
    -- 建议提前创建索引（无需在存储过程中重复执行）
    -- CREATE INDEX idx_sub_status_cancel_id ON Sub_status(status, id);
    -- CREATE INDEX idx_sub_date_id ON subs(date, id);

    LOOP
        DELETE /*+ PARALLEL(subs, 8) */ -- 并行度按需调整，比如4/8
        FROM subs
        INNER JOIN Sub_status ss ON subs.id = ss.id
        WHERE ss.status = 'cancel'
          AND subs.date <= v_retention_date
          AND subs.id > v_last_id -- 基于主键范围分页，避免重复删除
          AND ROWNUM <= v_batch_size;

        v_rows_deleted := SQL%ROWCOUNT;
        v_total_deleted := v_total_deleted + v_rows_deleted;

        -- 更新上次删除的最大主键ID，确保下一批范围正确
        SELECT NVL(MAX(id), v_last_id) INTO v_last_id
        FROM subs
        INNER JOIN Sub_status ss ON subs.id = ss.id
        WHERE ss.status = 'cancel'
          AND subs.date <= v_retention_date
          AND subs.id <= v_last_id + v_batch_size;

        -- 持久化删除日志（需提前创建delete_log表）
        INSERT INTO delete_log (delete_time, batch_rows, total_rows, status)
        VALUES (SYSDATE, v_rows_deleted, v_total_deleted, 'SUCCESS');

        COMMIT;

        EXIT WHEN v_rows_deleted = 0;

        -- 可选：批量删除后休眠，降低系统资源占用
        DBMS_LOCK.SLEEP(5);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Total deleted rows: ' || v_total_deleted);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        -- 记录错误日志
        INSERT INTO delete_log (delete_time, batch_rows, total_rows, status, error_msg)
        VALUES (SYSDATE, v_rows_deleted, v_total_deleted, 'FAILED', SQLERRM);
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;

3. 额外优化建议 #

• 临时禁用归档日志：若已完成数据备份，可执行ALTER TABLE subs NOLOGGING;减少redo日志生成，删除完成后改回LOGGING
• 低峰期执行：选择业务流量最低的时段运行，避免影响在线业务
• 动态调整批量大小：若删除过程中出现回滚段不足、IO过高，可缩小v_batch_size至100-200万条
• 预校验待删数据：先执行SELECT COUNT(*) FROM subs JOIN Sub_status ss ON subs.id=ss.id WHERE ss.status='cancel' AND subs.date<=v_retention_date;确认数据量，评估执行时长

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