Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

User_IN7XHFeb 18 2021

We have an Oracle Database running v. 18c, with a table containing a column with Datatype RAW(16).
image.pngThe 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
image.pngThe equivalent field in .NET is setup like this
image.png
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
image.png
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
image.pngEF Core version: 3.1.10
Database provider: Oracle.Manageddataaccess
IDE: Visual studio 2019

Comments

Processing

Post Details

Added on Feb 18 2021
2 comments
1,171 views