Oracle未命中索引问题：EF与Devart DotConnect生成SQL的性能差异

问题场景 #

涉及的表约有3000万条记录，使用Entity Framework编写以下LINQ查询：

dbContext.MyTable.FirstOrDefault(t => t.Col3 == "BQJCRHHNABKAKU-KBQPJGBKSA-N");

Devart DotConnect for Oracle自动生成的SQL如下：

SELECT
Extent1.COL1,
Extent1.COL2,
Extent1.COL3
FROM MY_TABLE Extent1
WHERE (Extent1.COL3 = :p__linq__0) OR ((Extent1.COL3 IS NULL) AND (:p__linq__0 IS NULL))
FETCH FIRST 1 ROWS ONLY

该查询耗时约4分钟，明显执行了全表扫描。

而手动编写的SQL：

SELECT
Extent1.COL1,
Extent1.COL2,
Extent1.COL3
FROM MY_TABLE Extent1
WHERE Extent1.COL3 = :p__linq__0
FETCH FIRST 1 ROWS ONLY

仅需200毫秒即可返回匹配结果。

疑问 #

为何会出现这种差异？预期查询优化器会识别当参数非空时右侧条件为假，为何第一个查询未命中索引？

原因分析 #

• Oracle绑定变量窥探的局限性：Oracle的绑定变量窥探机制在处理带OR的复合条件时，无法精准预判参数实际值对应的条件分支。虽然执行时:p__linq__0是非空值，但优化器生成执行计划时会考虑参数可能为NULL的场景，认为需要覆盖两种情况，最终选择了全表扫描这种通用但低效的执行计划，而非针对非空参数的索引扫描。
• 索引匹配的条件限制：针对COL3的常规B树索引无法直接匹配COL3 IS NULL的分支，优化器会因为这个分支的存在，判定使用索引的收益不足以覆盖所有场景，因此放弃索引扫描。
• EF提供商的默认生成逻辑：Devart DotConnect for Oracle生成带空值兼容的OR条件，是为了兼容LINQ中==运算符处理null的逻辑（比如参数为null时查询列值也为null的记录），但这种通用逻辑在非空固定值查询场景下，反而干扰了Oracle优化器的判断。

解决建议 #

• 若确定查询参数不会为null，可通过EF扩展方法或自定义SQL避免生成空值兼容条件，例如使用Where(t => t.Col3.Equals("BQJCRHHNABKAKU-KBQPJGBKSA-N"))（部分EF提供商将生成更简洁的SQL），或直接执行手动编写的SQL语句。
• 若确实需要支持空值查询，可针对COL3创建包含空值场景的函数索引，但需评估索引维护成本。
• 可在生成的SQL中添加索引提示/*+ INDEX(MY_TABLE 你的索引名) */强制优化器使用索引，但需谨慎使用，避免影响其他场景的执行计划。

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