Oracle中子查询与字面量查询的SQL性能异常差异问题咨询

• 执行计划成本估算偏差
  第一条使用字面量2010的查询，CBO优化器生成执行计划时可以直接基于x字段的统计信息、直方图数据精准判断过滤性，默认选择走x字段的索引扫描，成本计算完全准确。第二条查询的过滤值是子查询返回的动态结果，优化器生成主查询执行计划时无法提前获取具体值，只能基于通用规则估算过滤性，很容易误判为全表扫描成本更低，主动放弃走x字段的索引，最终导致全表扫描耗时飙升。
• 子查询展开失败引发重复执行
  Oracle优化器默认会尝试将非关联标量子查询展开为表关联操作，和主表做关联查询复用子查询结果。如果存在yearTable统计信息过时、子查询涉及隐式类型转换、yearTable配置了行级安全策略等情况，优化器会放弃子查询展开，生成嵌套循环执行逻辑：主表每扫描一行就执行一次子查询，即使单次子查询耗时仅毫秒级，主表数据量较大时累计开销也会远高于单次索引查询。
• 执行计划固化导致复用错误
  如果该类子查询语句触发了隐式绑定变量转换，首次执行时如果子查询返回值的过滤性极差，优化器生成了全表扫描的执行计划并固化到游标缓存中，后续即使子查询返回高过滤性的值，也会直接复用错误的执行计划，不会重新计算成本选择索引路径。

快速验证方法：执行第二条语句时增加索引强制hint，对比执行耗时是否回归正常，示例：

select /*+ index(你的主表名 x字段对应的索引名) */ * from 你的主表名
where x = (select year from yearTable where ID=1234);

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