Hi all,
I am developing an application using .Net framework 4.5, 64-bit ODAC 12c Release 1 for Windows x64 and Entity Framework against a 11gR2 database.
Trying to address some major performance issues, I discovered that the following LINQ query
Int16 month = 5;
Int16 year = 2014;
query.Where(q => q.EQIZM == month && q.AQIZM == year);
turns into this strange SQL:
SELECT * FROM CZD05T00
WHERE ((( CAST( "Extent1"."AQIZM" AS number(10,0))) = 2014) AND (( CAST( "Extent1"."EQIZM" AS number(10,0))) = 5))
That is the cause of my issues, because due to the CASTs an index on AQIZM , EQIZM is not used.
Here the relevant exccerpt of CZD05T00 definition:
CREATE TABLE CZD05T00
(
KQIZA NUMBER (18) PRIMARY KEY NOT NULL ,
AQIZM NUMBER (4) NOT NULL ,
EQIZM NUMBER (2) NOT NULL ,
NPGXV_QIZM NUMBER (3) NOT NULL ,
CAGZI VARCHAR2 (4 BYTE) NOT NULL ,
CAPCX_PRFG VARCHAR2 (2 BYTE) NOT NULL ,
<cut>
);
CREATE INDEX CZD05X52 ON CZD05T00
(AQIZM ASC,EQIZM ASC);
Anybody knows how to avoid the generation of these unnecessary CASTs, so that the correct index is used, instead of a full table scan?
Thanks in advance,
Gianfranco