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

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

Postby 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.
Bart Vanryckeghem
 
Posts: 1
Joined: Fri 19 Jun 2015 08:10

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

Postby 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: 7391
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

Postby 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: 7391
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

Postby 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.
Shalex
Devart Team
 
Posts: 7391
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle