Oracle 子查询执行远慢于等值查询的性能差异原因问询

现有如下两条SQL查询语句：

select * from PRE_DETAIL_REPORT a where item = (select item from apple_skus);
select * from PRE_DETAIL_REPORT a where item IN ('100299122');

其中APPLE_SKUS表仅存储1条item数据，值为100299122，实际执行时第一条查询耗时2分钟，第二条仅耗时3秒。

• 子查询未被优化器扁平化重写
  绝大多数数据库默认会把未做显式关联的标量子查询判定为关联子查询，即对PRE_DETAIL_REPORT表的每一行数据，都重复执行一次apple_skus表的查询操作。如果PRE_DETAIL_REPORT表数据量达到百万级以上，即使单条子查询耗时只有1毫秒，叠加百万次调用后的总耗时也会达到分钟级。
  而第二条语句直接传入常量值，优化器可以直接基于PRE_DETAIL_REPORT表的item字段索引做精准定位，仅需一次索引查找就能匹配到所有符合条件的行，执行效率极高。
• 隐式转换导致索引失效
  如果apple_skus表的item字段和PRE_DETAIL_REPORT表的item字段类型不匹配（例如一个为INT类型，一个为VARCHAR类型），子查询返回的结果每次和主表字段匹配时都需要做隐式类型转换，不仅会导致item字段的索引失效触发全表扫描，还会额外增加每一行匹配的计算开销。
  而第二条语句的常量会自动转换为和PRE_DETAIL_REPORT表item字段匹配的类型，不会产生额外的转换开销，即使没有索引，纯全表扫描的速度也远快于第一条语句。
• 元数据统计信息不准确
  如果apple_skus表的统计信息未及时更新，优化器会误判子查询返回的行数，不会选择最优的执行计划将子查询结果提前固化为常量，反而会选择嵌套循环的关联方式，进一步放大查询开销。

可以将第一条语句改写为JOIN形式，引导优化器选择更优的执行计划：

select a.* from PRE_DETAIL_REPORT a
join apple_skus b on a.item = b.item;

如果确认apple_skus表只会返回唯一的item值，也可以直接用常量替换子查询，和第二条语句执行效率一致。

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