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!
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
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))))))