Linq statement forces a full table scan when using Oracle RAW(16) field in the where clause

[Bart Vanryckeghem]

Hi,

I'm having the following issue with a Linq statement that forces a full table scan using dotconnect for oracle 8.1.26

Table:

Code: Select all

CREATE TABLE ZMC_WMS_TSK (
    TSK_ID           NUMBER           NOT NULL,
    STTS_CD          VARCHAR2(2 BYTE) NOT NULL,
    ZMS_SMPLNG_ID    NUMBER,
    SESSION_ID       RAW(16),
    PRIMARY KEY (TSK_ID)
)

CREATE INDEX INDEX_ZMC_WMS_TSK_SESSION_ID ON ZMC_WMS_TSK (SESSION_ID)
TABLESPACE TABS_OCS1
STORAGE (INITIAL 64 K
         NEXT 1 M
         MAXEXTENTS UNLIMITED)
LOGGING;

Linq statement

Code: Select all

Guid guid = new Guid("28b582c5-dec8-4305-aaa0-3cdfcaab0457");
var tsk = ZMC_WMS_TSK.FirstOrDefault(x => x.SESSION_ID == guid);

The generated SQL from Linq is here

Code: Select all

SELECT 
"Extent1".TSK_ID,
"Extent1".STTS_CD,
"Extent1".ZMS_SMPLNG_ID,
"Extent1".SESSION_ID
FROM ZMC_WMS_TSK "Extent1"
WHERE ("Extent1".SESSION_ID = 'C582B528C8DE0543AAA03CDFCAAB0457')

If you look at the oracle sql execution plan you see that this statement forces a full table scan.
The index on field SESSION_ID is not being used

If i try the SQL statement in toad after adding an hextoraw conversion in the select statement then
the sql execution plan tells me Oracle now uses the index on field SESSION_ID

Code: Select all

SELECT 
"Extent1".TSK_ID,
"Extent1".STTS_CD,
"Extent1".ZMS_SMPLNG_ID,
"Extent1".SESSION_ID
FROM ZMC_WMS_TSK "Extent1"
WHERE ("Extent1".SESSION_ID = hextoraw('C582B528C8DE0543AAA03CDFCAAB0457'))

How can dotconnect for oracle generate by default the hextoraw conversion when converting LINQ to SQL
What do i need to do here ?

Kind Regards,
Bart Vanryckeghem

[Shalex]

Thank you for your report. We will investigate the question and notify you about the result.

[Shalex]

The SQL generation for LINQ queries with comparison based on the RAW(16) column is improved: explicit cast to RAW(16) is used now to make Oracle Server employ an existing index created for the RAW(16) column in Oracle execution plan. We will notify you when the corresponding build of dotConnect for Oracle is available for download.

[Shalex]

New build of dotConnect for Oracle 8.4.447 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=32065.