使用IN条件删除30万条遗留表记录性能受损，求高效优化方案

以下几种方法可以替代原有的IN子句删除逻辑，提升性能并规避锁表、日志过载等问题：

1. 用EXISTS替代IN #

IN子句处理大结果集时易产生低效执行计划，而EXISTS采用半连接逻辑，找到匹配记录后立即停止检索，通常更高效：

DELETE FROM LEG_EMP e
WHERE EXISTS (
    SELECT 1 FROM EMP_REF r
    WHERE r.ROW_ID = e.EMP_ID
);

2. 分批删除 #

一次性删除30万条记录会生成大量undo/redo日志，导致锁表时间过长，分批删除可缓解该问题：

DECLARE
  v_batch_size NUMBER := 10000; -- 每次删除的记录数，可根据系统负载调整
  v_rows_deleted NUMBER := 1;
BEGIN
  WHILE v_rows_deleted > 0 LOOP
    DELETE FROM LEG_EMP e
    WHERE EXISTS (
        SELECT 1 FROM EMP_REF r
        WHERE r.ROW_ID = e.EMP_ID
    )
    AND ROWNUM <= v_batch_size;
    
    v_rows_deleted := SQL%ROWCOUNT;
    COMMIT; -- 每批提交释放undo空间
  END LOOP;
END;
/

优点：降低单批次日志生成量，缩短锁表时长，减少对业务的影响。

3. 使用JOIN直接删除（Oracle 12c+） #

Oracle 12c及以上支持DELETE ... USING语法，直接通过表关联执行删除，能更高效利用索引：

DELETE FROM LEG_EMP e
USING EMP_REF r
WHERE e.EMP_ID = r.ROW_ID;

若为Oracle 12c以下版本，可采用子查询关联写法（需确保关联结果集可更新，比如关联字段为主键/唯一约束）：

DELETE FROM (
    SELECT e.* FROM LEG_EMP e
    INNER JOIN EMP_REF r ON e.EMP_ID = r.ROW_ID
);

4. 临时表+截断重建（适合离线场景） #

若允许短暂停止业务，且保留的记录少于删除量，这种方法速度最快：

1. 导出需保留的记录到临时表：

CREATE GLOBAL TEMPORARY TABLE LEG_EMP_TEMP
AS SELECT * FROM LEG_EMP e
WHERE NOT EXISTS (
    SELECT 1 FROM EMP_REF r
    WHERE r.ROW_ID = e.EMP_ID
);

2. 截断原表（DDL操作，无undo日志，速度极快）：

TRUNCATE TABLE LEG_EMP;

3. 将保留记录插回原表：

INSERT INTO LEG_EMP SELECT * FROM LEG_EMP_TEMP;
COMMIT;

额外注意事项 #

• 检查执行计划：用EXPLAIN PLAN FOR查看执行计划，确认是否用到EMP_ID和ROW_ID上的索引，避免全表扫描。
• 索引维护：大量删除后，建议重建或收缩索引消除碎片，例如：ALTER INDEX idx_leg_emp_emp_id REBUILD;
• 时间选择：尽量在业务低峰期执行删除操作，减少对线上业务的干扰。

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