Oracle 12cR2嵌套循环与反连接查询返回错误结果问题咨询

问题成因 #

• 该异常为Oracle 12.2.0.1版本的已知原生BUG Bug 26636238，触发条件为查询执行计划生成**NESTED LOOPS（嵌套循环）+ ANTI JOIN（反连接）**组合路径时，优化器的列投影计算逻辑存在缺陷：
  • 当外层查询的列选择集合触发错误的投影修剪逻辑时，优化器会错误过滤全部符合条件的行，导致返回0行
  • 当查询添加ORDER BY子句时，会强制优化器调整执行计划逻辑，触发排序前的全量结果集抓取，因此会变更最终返回行数
• 该BUG仅受统计信息分布、子查询嵌套层级影响，和数据损坏、缓存异常、存储层配置无关。

解决方案 #

临时 workaround（无需补丁，即时生效） #

以下方案任选其一即可：

• 会话级禁用反连接的嵌套循环执行路径，执行命令：
  ALTER SESSION SET "_optimizer_nestloop_anti_join" = FALSE;
  如需全局生效可执行：ALTER SYSTEM SET "_optimizer_nestloop_anti_join" = FALSE SCOPE=SPFILE;，执行后需重启数据库生效
• 给问题查询添加Hint /*+ opt_param('_optimizer_nestloop_anti_join','false') */，仅针对当前查询生效，不影响其他业务SQL
• 固定使用返回正常结果的列集合查询，或添加/*+ no_eliminate_oby */ Hint强制保留排序逻辑

永久修复方案 #

• 升级数据库至12.2.0.1版本的最新RU（Release Update）补丁集，或直接升级至19c长支持版本，该BUG已在后续版本迭代中正式修复
• 针对Oracle 12.2.0.1版本可单独安装Patch 26636238补丁，安装前需核对当前数据库的RU版本与补丁兼容性。

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