PLSQL游标循环删除多表数据耗时过长，有什么优化提速方案？

你当前性能瓶颈的核心是逐行循环执行单条删除（RBAR模式），1万次循环会产生26万次PL/SQL到SQL引擎的上下文切换，开销极高，可通过以下方案优化：

• 优先改用集合批量删除
  先将游标查询到的待删除主键集合存入会话级全局临时表(GTT)，可额外给临时表的主键字段加索引，之后26张表各执行1次关联删除即可，不需要循环：

  -- 先创建会话级临时表（提前建好，不用每次执行都建）
  CREATE GLOBAL TEMPORARY TABLE gtt_del_ids (col1 xxx, col2 xxx, col3 xxx) ON COMMIT PRESERVE ROWS;
  -- 存储待删ID
  INSERT INTO gtt_del_ids SELECT a.col1,a.col2,a.col3 FROM a,b WHERE a.col=b.col;
  -- 单条语句删全量匹配数据，26张表各执行1次即可
  DELETE FROM tab2 WHERE cola IN (SELECT col1 FROM gtt_del_ids);
  

  该方案性能提升最明显，删除1万条数据的执行时间可以压缩到分钟级甚至秒级。

• 需保留逐行日志的场景用FORALL批量绑定
  如果你需要保留现有逐行打印删除结果、捕获单条错误的逻辑，可改用批量fetch+FORALL语法，减少上下文切换开销：

  DECLARE
    TYPE rec_del IS RECORD (col1 xxx, col2 xxx, col3 xxx);
    TYPE tab_del IS TABLE OF rec_del;
    t_del tab_del;
    CURSOR c_invoice IS SELECT a.col1,a.col2,a.col3 FROM a,b WHERE a.col=b.col;
  BEGIN
    OPEN c_invoice;
    LOOP
      -- 一次批量fetch1000条，可根据实际调整批次大小
      FETCH c_invoice BULK COLLECT INTO t_del LIMIT 1000;
      EXIT WHEN t_del.COUNT = 0;
      
      -- 批量执行删除，SAVE EXCEPTIONS用于捕获单条执行异常不中断整体
      FORALL i IN 1..t_del.COUNT SAVE EXCEPTIONS
        DELETE FROM tab2 WHERE cola = t_del(i).col1;
      
      -- 此处可遍历SQL%BULK_EXCEPTIONS打印错误日志，遍历t_del打印成功/未找到记录日志
    END LOOP;
    CLOSE c_invoice;
  END;
  

  该方案可兼容你现有的日志需求，性能比逐行循环提升10~100倍。

• 超大数据量表的特殊优化
  针对6000万条级别的大表，如果单次待删除数据占表总量的10%以上，可采用换表方案替代DELETE：

  1. 创建与原表结构一致的临时表
  2. 将原表中不需要删除的数据插入临时表
  3. 重命名原表为备份表，临时表重命名为原表
  4. 重建原表的索引、约束、权限
     该方案删除效率比DELETE高数个量级，适合离线批处理场景。

• 辅助优化项

  • 除了触发器外，可临时禁用待删除表的外键约束、非主键索引，删除完成后再重新启用/重建，避免删除过程中额外的维护开销
  • 可开启批量提交逻辑，每处理1000~5000条提交一次，通过开关控制模拟模式下不执行真实COMMIT即可，既兼容模拟需求，又避免单事务过大导致UNDO表空间占用过高

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