Oracle SQL如何编写无日志DELETE语句批量删除大量数据？

首先要明确：关系型数据库不存在完全无日志的DELETE操作，所有数据修改操作都会生成事务日志保证ACID特性。你可以通过分批删除+最小化日志配置的方式降低日志开销，避免一次性删除导致的事务过大、锁表、磁盘占满问题。

一、分批删除实现方案 #

你的原语句一次性命中600万行，会生成超大事务，建议调整为每次删除固定行数，循环执行直到匹配条件的数据全部删完：

Oracle 分批删除示例 #

DECLARE
    v_rows_deleted NUMBER := 1;
BEGIN
    WHILE v_rows_deleted > 0 LOOP
        DELETE FROM MyTable
        WHERE TO_CHAR(MyDate, 'YYYYMM') = 202101
        AND ROWNUM <= 10000; -- 每次删除1万行，可根据服务器性能调整为5千~5万
        v_rows_deleted := SQL%ROWCOUNT;
        COMMIT; -- 每次提交释放事务空间，减少日志累积
    END LOOP;
END;
/

SQL Server 分批删除示例 #

WHILE 1=1
BEGIN
    DELETE TOP (10000) FROM MyTable
    WHERE FORMAT(MyDate, 'yyyyMM') = '202101'
    IF @@ROWCOUNT = 0 BREAK;
    COMMIT;
END

MySQL 分批删除示例 #

WHILE EXISTS (SELECT 1 FROM MyTable WHERE DATE_FORMAT(MyDate, '%Y%m') = '202101') DO
    DELETE FROM MyTable
    WHERE DATE_FORMAT(MyDate, '%Y%m') = '202101'
    LIMIT 10000;
    COMMIT;
END WHILE;

二、降低日志开销的优化手段（接近无日志效果） #

• 如果要删除的数据占表总数据的30%以上，建议先把需要保留的数据备份到临时表，再用TRUNCATE操作清空原表，最后把保留数据插回：TRUNCATE是DDL操作，只会产生极少量的元数据日志，开销远低于DELETE

  注意：TRUNCATE会清空全表，且无法回滚，操作前必须确认备份数据正确

• 调整数据库日志模式：Oracle可以将表设置为NOLOGGING模式、SQL Server设置为简单恢复模式、MySQL可临时关闭非必要的二进制日志（操作完成后记得恢复原配置）
• 删除前先删除目标表上的非必要索引、触发器，删除完成后再重建：索引和触发器的维护会产生大量额外日志
• 尽量避免在删除条件中对字段用函数转换，比如把TO_CHAR(MyDate, 'YYYYMM') = 202101改成MyDate BETWEEN DATE'2021-01-01' AND DATE'2021-01-31'，可以利用MyDate上的索引加快删除速度，减少锁持有的时间

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