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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Bart Vanryckeghem
Posts: 1
Joined: Fri 19 Jun 2015 08:10

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

Post by Bart Vanryckeghem » Fri 19 Jun 2015 09:14

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
Last edited by Bart Vanryckeghem on Wed 01 Jul 2015 07:23, edited 4 times in total.

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

Re: Select Oracle where clause with RAW(16) fields

Post by Shalex » Mon 22 Jun 2015 12:26

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

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Wed 01 Jul 2015 11:53

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
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Thu 02 Jul 2015 14:47

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.

Post Reply