Forum Stats

  • 3,728,252 Users
  • 2,245,583 Discussions
  • 7,853,410 Comments

Discussions

Need help: Index on RAW(16) does not trigger when comparing to GUID from .NET

User_IN7XH
User_IN7XH Member Posts: 1 Green Ribbon

We have an Oracle Database running v. 18c, with a table containing a column with Datatype RAW(16).

The Datatype was chosen by Entity Framework using Code-first implementation of the GUID datatype in .NET

We have set up an index on the column

The equivalent field in .NET is setup like this


When running a query, looking for specific EJERSKIFTE_ID based on the GUID value, the query will not use the index. If instead we surround each representation of the GUID value with "HEXTORAW()" It will correctly use the index.


Original query (Not triggering index):

SELECT "e"."OBJECTID" FROM "GSTEJF"."EJERSKABSSKIFTE" "e" WHERE (((((("e"."EJERSKIFTE_ID" IN ('AB6AF396EB644B17B517D698489B16D9', 'B7E9D9A8526541C7BA65DD3E314FB6C0')) AND ("e"."REGISTRERINGFRA" <= :transactionTime_1))) AND ((("e"."REGISTRERINGTIL" > :transactionTime_2) OR ("e"."REGISTRERINGTIL" IS NULL))))) AND ((("e"."VIRKNINGFRA" <= :validTime_3) AND ((("e"."VIRKNINGTIL" > :validTime_4) OR ("e"."VIRKNINGTIL" IS NULL))))))

And corresponding Execution-plan


Modified query (Correctly triggering index):

SELECT "e"."OBJECTID" FROM "GSTEJF"."EJERSKABSSKIFTE" "e" WHERE (((((("e"."EJERSKIFTE_ID" IN (HEXTORAW('AB6AF396EB644B17B517D698489B16D9'), HEXTORAW('B7E9D9A8526541C7BA65DD3E314FB6C0'))) AND ("e"."REGISTRERINGFRA" <= :transactionTime_1))) AND ((("e"."REGISTRERINGTIL" > :transactionTime_2) OR ("e"."REGISTRERINGTIL" IS NULL))))) AND ((("e"."VIRKNINGFRA" <= :validTime_3) AND ((("e"."VIRKNINGTIL" > :validTime_4) OR ("e"."VIRKNINGTIL" IS NULL))))))

And corresponding Execution-plan

EF Core version: 3.1.10

Database provider: Oracle.Manageddataaccess

IDE: Visual studio 2019

Sign In or Register to comment.