为何Oracle中两个高度相似的查询会生成差异极大的执行计划

核心差异来自过滤条件的写法对Oracle优化器规则的触发差异 #

• 函数包裹导致谓词下推优化失效
  两个查询唯一的区别就是查询1的nivelriesgo字段包了一层COALESCE函数，查询2直接用原始字段做过滤。Oracle优化器对于直接操作裸字段的过滤条件，可以执行谓词下推优化：把nivelriesgo NOT IN ('N',' ')这个过滤逻辑提前到相关子查询执行之前，先筛掉不符合要求的行，再去取每个idactorreportado对应的最大fechariesgo，需要计算的行数少了，自然会选更高效的索引扫描路径。
  而加了COALESCE之后，优化器没法直接识别这个函数转换后的过滤逻辑和原始字段的对应关系，没法做谓词下推，只能先跑完所有相关子查询拿到每个ID的最新风险记录，再对结果集做函数运算后过滤，执行顺序完全变了，执行计划自然天差地别。
• NULL三值逻辑和统计信息估算偏差
  看建表语句你这个nivelriesgo字段没有非空约束，允许存NULL。Oracle里NOT IN只要涉及NULL值，运算结果就是UNKNOWN，等价于不满足条件：查询2的nivelriesgo NOT IN ('N',' ')天然会过滤掉NULL、'N'、' '三种值，优化器可以直接用nivelriesgo字段上的统计信息准确估算过滤后剩下的行数，刚好你的表主键是(IDACTORREPORTADO, FECHARIESGO)，所以会直接走主键索引的高效路径。
  而查询1用COALESCE把NULL转成空格再判断，虽然逻辑上和查询2过滤结果完全一样，但函数包裹导致优化器没法用原始字段的统计信息，行数估算出了偏差，最终选了全表扫描再加后续过滤的路径。
• 小表场景的放大效应
  你这个表总共才15行，属于极小表，优化器的行数估算哪怕差个几行，也会直接改变执行计划的选择，这也是差异被放大的原因之一。

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