无索引超大表删除少量数据耗时过长优化方案咨询

为什么现在删300条要跑3小时 #

慢的核心原因非常明确：

• 无索引前提下，游标逐行处理的逻辑会触发多次无效全表扫描，2.3亿行数据遍历的IO开销本身就极高
• 逐行游标删除会产生大量细碎事务、行锁和递归调用，平白多了数倍的额外开销

可落地优化方案（完全满足原表不建任何索引的要求） #

• 方案1：用ROWID批量删除，直接替换逐行游标逻辑
  ROWID是Oracle表每行自带的物理地址标记，不需要在原表上建任何索引，就能通过ROWID直接精准定位到对应行，是无索引场景下最快的行定位方式。
  核心逻辑是只做1次表扫描，找够300条符合删除条件的记录就立刻停止扫描，不会硬扫完2.3亿行；捞到待删行的ROWID后，直接按ROWID删，不需要再二次扫表。参考实现代码：

  -- 建独立临时表存待删行的ROWID，不属于原大表的索引，完全符合客户要求
  CREATE GLOBAL TEMPORARY TABLE t_tmp_del (row_id ROWID) ON COMMIT PRESERVE ROWS;
  
  -- 加FIRST_ROWS提示，找到300条符合条件的记录就立刻终止扫描
  INSERT /*+ FIRST_ROWS(300) */ INTO t_tmp_del
  SELECT ROWID
  FROM 你的业务大表
  WHERE 配置的删除筛选条件
  AND ROWNUM <= 300;
  
  -- 按ROWID直接定位删除，无额外全表扫描开销
  DELETE FROM 你的业务大表 t
  WHERE EXISTS (SELECT 1 FROM t_tmp_del d WHERE d.row_id = t.ROWID);
  
  COMMIT;
  

  这个方案比游标逐行删的性能高10到100倍很正常，单次删除耗时基本能压到分钟级。

• 方案2：维护已扫描块的记录，越跑越快
  你每天只需要删300条，符合删除条件的记录占比极低，99%以上的数据块扫过一次就能确认里面没有待删记录，根本没必要每次删都重复扫。
  你可以建一个独立的小元数据表（同样不属于原表的索引，不违反客户要求），记录每次扫过、确认没有待删记录的数据块ID，下次跑删除任务的时候直接跳过这些块，只扫没排查过的块、以及上次跑完之后新增的数据块。
  按这个逻辑跑1到2周之后，每次需要扫的数据块量会降到非常低的水平，后续删除耗时能稳定在秒级。

• 方案3：低峰期开并行扫描，进一步压短耗时
  如果删除作业能安排在业务低峰跑，可以给查询语句加/*+ PARALLEL(8) */的并行提示，启动多个并行进程同时扫表，最快速度找齐300条待删记录。注意删除的时候要攒够300条一次性提交，别逐行提交，能省很多事务日志和锁的开销。

注意避坑 #

• 别再用CURSOR FOR逐行循环删：逐行取数、逐行删除、逐行提交的模式，会产生大量没必要的IO和事务开销，是无索引大表删除场景下效率最低的写法。
• WHERE后面的删除条件尽量简化，别加复杂的函数计算，减少每行扫描时的判断成本，让数据库能最快匹配到符合条件的行。
• 如果后续能和客户沟通确认，允许对原表做范围分区（注意是表分区，不是建分区索引，不属于客户禁止的操作范畴），可以直接按删除条件做分区，之后删数据直接DROP对应分区就行，能做到毫秒级删除，但这个需要客户放开表结构修改权限才能做。

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