Oracle查询性能求助：IN子查询与固定参数耗时差异问题

这问题我碰到过好多次了，典型的Oracle执行计划因输入方式不同跑偏的情况，咱们一步步来排查和优化：

当你用固定LOTID值（比如'CO5383.1'）查询时，Oracle的成本优化器（CBO）能精准估算匹配的行数，直接选择最优的执行路径——比如走LOTID对应的索引、触发分区裁剪（如果视图底层是分区表的话）。但换成IN子查询时，CBO可能因为统计信息不准、对子查询的行数估算错误，或者无法识别子查询里的固定值集合，导致选了低效的执行计划（比如全表扫描、嵌套循环变成哈希连接但资源不够）。

这是最关键的一步，得先看Oracle到底在两种场景下用了什么执行路径：

• 分别执行以下命令获取执行计划：

  -- 固定值查询的执行计划
  EXPLAIN PLAN FOR SELECT * FROM TRES_RAWDATA_LOT WHERE LOTID IN ('CO5383.1', 'CO5384.1', 'CO5385.1');
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
  
  -- 子查询的执行计划
  EXPLAIN PLAN FOR SELECT * FROM TRES_RAWDATA_LOT WHERE LOTID IN (SELECT LOTID FROM SAPLOTID);
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
  

• 重点对比：
  • 访问视图TRES_RAWDATA_LOT的方式（是INDEX RANGE SCAN还是TABLE ACCESS FULL）
  • 行数估算值（Rows列）和实际返回行数的差距
  • 连接方式（如果视图是多表连接）、是否触发了分区裁剪

Oracle的CBO完全依赖统计信息选执行计划，统计信息旧了必然出问题：

1. 检查SAPLOTID表的统计信息更新时间：

   SELECT LAST_ANALYZED, NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME = 'SAPLOTID';
   

   如果LAST_ANALYZED是几天前甚至更久，或者NUM_ROWS和实际行数差很多，就更新统计信息：

   EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'SAPLOTID', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);
   

2. 同样检查视图TRES_RAWDATA_LOT底层所有表的统计信息，用相同的命令更新。

方案1：用JOIN代替IN子查询 #

Oracle对IN子查询的优化有时候不如显式JOIN，尤其是子查询表数据量不大时：

-- 加DISTINCT避免SAPLOTID有重复LOTID导致结果集重复
SELECT DISTINCT r.* 
FROM TRES_RAWDATA_LOT r
JOIN SAPLOTID s ON r.LOTID = s.LOTID;

如果SAPLOTID里的LOTID没有重复，可以去掉DISTINCT，性能会更好。

方案2：临时表+索引 #

把SAPLOTID的LOTID先提取到临时表，再加索引，让Oracle能精准定位：

-- 创建临时表（会话级，退出自动清空）
CREATE GLOBAL TEMPORARY TABLE TMP_LOTID (LOTID VARCHAR2(50)) ON COMMIT PRESERVE ROWS;
INSERT INTO TMP_LOTID SELECT DISTINCT LOTID FROM SAPLOTID;
-- 给临时表加索引
CREATE INDEX IDX_TMP_LOTID ON TMP_LOTID(LOTID);

-- 用临时表查询
SELECT * FROM TRES_RAWDATA_LOT WHERE LOTID IN (SELECT LOTID FROM TMP_LOTID);

方案3：强制分区裁剪（如果视图底层是分区表） #

如果TRES_RAWDATA_LOT底层是按LOTID分区的表，子查询可能没触发分区裁剪，试试用hint强制：

SELECT /*+ PARTITION(r) */ * 
FROM TRES_RAWDATA_LOT r
WHERE LOTID IN (SELECT LOTID FROM SAPLOTID);

方案4：重新生成执行计划 #

如果统计信息没问题，但执行计划还是跑偏，可以用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO()刷新监控信息，或者重新生成视图的执行计划：

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

如果是视图有 stale 的执行计划，也可以重新编译视图：

ALTER VIEW TRES_RAWDATA_LOT COMPILE;

• 检查SAPLOTID表是否有大量重复的LOTID，先做SELECT DISTINCT LOTID FROM SAPLOTID去重后再用，能减少Oracle的处理量。
• 如果视图TRES_RAWDATA_LOT本身很复杂（多表连接、嵌套子查询），可以考虑把视图改造成物化视图，提前预计算结果。

内容的提问来源于stack exchange，提问作者Davees John Baclay