Oracle中Delete语句执行计划查看方法及两种删除实现方案

一、如何查看Delete语句的执行计划 #

Oracle的EXPLAIN PLAN并非仅支持SELECT语句，DML语句（包括DELETE）同样可以通过以下方法查看执行计划：

1. 使用EXPLAIN PLAN FOR + DBMS_XPLAN
   将目标DELETE语句单独提取，执行以下命令：

EXPLAIN PLAN FOR
DELETE FROM table1 t1 WHERE t1.ftcode = 'XI-9873';

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

如果是PL/SQL块中的DELETE，只需把对应DELETE语句单独拿出来执行上述命令即可。

2. SQL*Plus AUTOTRACE工具
   开启AUTOTRACE后执行DELETE语句（若不想实际修改数据，执行后可ROLLBACK）：

SET AUTOTRACE ON;
DELETE FROM table1 t1 WHERE t1.ftcode = 'XI-9873';
ROLLBACK; -- 可选，取消删除操作

该方法会直接返回执行计划及统计信息（如逻辑读、物理读等）。

3. 图形化工具（SQL Developer/PL/SQL Developer）
   选中目标DELETE语句，点击工具中的"执行计划"按钮（通常是图标），即可可视化查看执行计划的详细内容。

4. 查看实际执行计划（已执行的DELETE）
   如果DELETE已经执行，可通过V$SQL_PLAN结合DBMS_XPLAN查看实际执行的计划：

-- 先找到目标SQL的SQL_ID
SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE '%DELETE FROM table1%';

-- 替换SQL_ID后查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('your_sql_id', NULL, 'ALLSTATS LAST'));

二、检查Delete语句性能的其他方法 #

除了查看执行计划，还可以通过以下方式排查DELETE的性能问题：

• 统计执行时间：在SQL*Plus中执行SET TIMING ON，执行DELETE语句后会显示耗时；也可以手动记录执行前后的时间戳。
• 优化统计信息：确保表和索引的统计信息是最新的，执行DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'your_schema', TABNAME => 'table1');让优化器生成更优的执行计划。
• 检查索引有效性：确认DELETE的WHERE条件列（如ftcode）是否有合适的索引，避免全表扫描。可通过执行计划中的"TABLE ACCESS FULL"判断是否存在全表扫描。
• 查看等待事件：执行DELETE时，查询V$SESSION_WAIT或V$ACTIVE_SESSION_HISTORY，检查是否存在锁等待、IO等待等瓶颈。
• 分批处理大批量删除：如果要删除的行数较多，一次性删除会占用大量回滚空间和资源，可采用分批删除（比如每次删1000行）：

DECLARE
  v_rows NUMBER;
BEGIN
  LOOP
    DELETE FROM table1 t1 WHERE t1.ftcode = 'XI-9873' AND ROWNUM <= 1000;
    v_rows := SQL%ROWCOUNT;
    COMMIT;
    EXIT WHEN v_rows = 0;
  END LOOP;
END;
/

• 检查回滚段使用：大批量删除会消耗大量回滚空间，可通过V$ROLLSTAT查看回滚段的使用情况，确保空间充足。

三、两种删除写法的分析 #

1. 使用WHERE EXISTS判断的删除写法 #

原代码问题：子查询中使用了外层DELETE的别名t1，导致别名冲突，子查询会始终返回true，最终删除表中所有行，逻辑错误。修正后的正确写法如下：

DECLARE
  FTCode VARCHAR(20) := 'XI-9873';
BEGIN
  DELETE FROM table1 t1
  WHERE EXISTS (SELECT 1 FROM table1 WHERE ftcode = FTCode);

  DELETE FROM table2 t2
  WHERE EXISTS (SELECT 1 FROM table2 WHERE ftcodet2 = FTCode);
END;
/

优缺点：无需额外的COUNT查询，直接完成判断与删除，效率更高；但需注意子查询的别名不要与外层冲突，避免逻辑错误。

2. 使用COUNT(*)判断的删除写法 #

DECLARE
  FTCode    VARCHAR(20) := 'XI-9873';
  FT_COUNT  NUMBER := 0;
BEGIN
  SELECT COUNT(*)
    INTO FT_COUNT
    FROM table1 t1
   WHERE t1.ftcode = FTCode;

  IF FT_COUNT > 0 THEN
    DELETE FROM table1 t1 WHERE t1.ftcode = FTCode;
  END IF;

  FT_COUNT := 0;

  SELECT COUNT(*)
    INTO FT_COUNT
    FROM table2 t2
   WHERE t2.ftcodet2 = FTCode;

  IF FT_COUNT > 0 THEN
    DELETE FROM table2 t2 WHERE t2.ftcodet2 = FTCode;
  END IF;
END;
/

优缺点：逻辑直观，但多了一次COUNT(*)查询，若表数据量大且无对应索引，会额外增加全表扫描的开销；同时COUNT与DELETE之间可能存在数据变更（如其他会话插入/删除符合条件的行），导致逻辑不一致。

推荐写法 #

若需要判断是否有行被删除，无需提前COUNT，可直接使用SQL%ROWCOUNT获取删除行数：

DECLARE
  FTCode VARCHAR(20) := 'XI-9873';
BEGIN
  DELETE FROM table1 t1 WHERE t1.ftcode = FTCode;
  IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('删除了' || SQL%ROWCOUNT || '行table1数据');
  END IF;

  DELETE FROM table2 t2 WHERE t2.ftcodet2 = FTCode;
  IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('删除了' || SQL%ROWCOUNT || '行table2数据');
  END IF;
END;
/

这种写法既避免了额外的COUNT开销，又能准确获取删除行数，同时避免竞态条件。

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