Oracle not using index, Entity Framework & Devart DotConnect for oracle

Question

The table in question has ~30mio records. Using Entity Framework I write a LINQ Query like this:

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

Devart DotConnect for Oracle generates this:

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

The query takes about four minutes, obviously a full table scan.

However, handcrafting this SQL:

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

returns the expected match in 200ms.

Question: Why is it so? I would expect the query optimizer to note that the right part is false if the parameter is not null, so why doesn't the first query hit the index?

Answer 1

Please set UseCSharpNullComparisonBehavior=false explicitly:

  var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
  config.QueryOptions.UseCSharpNullComparisonBehavior = false;

If this doesn't help, send us a small test project with the corresponding DDL script so that we can investigate the issue.