Re: Index not used, instead there is filter operation with NLSSORT function

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 2 Oct 2019 14:39:01 +0000
Message-ID: <CWLP265MB1748973475D68B9CEF9F31D6A59C0_at_CWLP265MB1748.GBRP265.PROD.OUTLOOK.COM>

>> what is surprising for me, in the sql plan from client app there is no HEXTORAW function.
>> Reminder, client app plan filter operation is:
>> 2 - filter((NLSSORT("FILEPATHHASH",'nls_sort=''BINARY_CI''')=NLSSORT(:2,'nls_sort=''BINARY_CI''')))

The difference is that the client is sending in a bind variable, your example is using a literal, so the optimizer knows that it can derive the constant that is the result of nlssort() to your input.

If you modelled in SQL*Plus by
variable mv varchar2(10)
exec :mv := 'ee76f6f';

Then queried for filepathhash = :mv

you'd see the nlssort() applied to the bind variable rather than the hextoraw() literal value.

Regards
Jonathan Lewis

---

From: grzegorzof_at_interia.pl <grzegorzof_at_interia.pl> Sent: 02 October 2019 13:17
To: Jonathan Lewis
Cc: oracle-l_at_freelists.org
Subject: Re: Index not used, instead there is filter operation with NLSSORT function

Thanks for all the answers, indeed after setting:

alter session set nls_sort = binary_ci;
 and below was neccessary
alter session set nls_comp = linguistic;

Oracle is not able to use index anymore and filter predicate for full table scan looks like this: 2 - filter(NLSSORT("FILEPATHHASH",'nls_sort=''BINARY_CI''')=HEXTORAW('66') AND NLSSORT("FILEPATH",'nls_sort=''BINARY_CI''')=HEXTORAW('97') )

I've trimmed hextoraw values to make response clear, what is surprising for me, in the sql plan from client app there is no HEXTORAW function. I've checked the bind values and it was of type :  VARCHAR2(2000) the column itself is :

FILEPATHHASH                              NOT NULL VARCHAR2(40 CHAR)

Reminder, client app plan filter operation is: 2 - filter((NLSSORT("FILEPATHHASH",'nls_sort=''BINARY_CI''')=NLSSORT(:2,'nls_sort=''BINARY_CI''')))

Regards.
Greg

Od: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> Do: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>; Wys³ane: 11:23 ¦roda 2019-10-02
Temat: Re: Index not used, instead there is filter operation with NLSSORT function

>
> I thought I had an example of this on my blog somewhere, but all I could find was a note about "nls based" indexes not behaving as nicely as they should - but there may be something there that you find useful: https://jonathanlewis.wordpress.com/2016/01/06/nls-mess/
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org on behalf of Jonathan Lewis
> Sent: 02 October 2019 10:16
> To: oracle-l_at_freelists.org
> Subject: Re: Index not used, instead there is filter operation with NLSSORT function
>
> It looks like the client has set:
>
> alter session set nls_sort = binary_ci;
> possibly with
> alter session set nls_comp = linguistic;
>
> This may mean you need an index on:
> (
> nlssort(filepathhash, 'nls_sort=''BINARY_CI''')
> )
>
> to get the same performance.
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org on behalf of grzegorzof_at_interia.pl
> Sent: 02 October 2019 10:06
> To: oracle-l_at_freelists.org
> Subject: Index not used, instead there is filter operation with NLSSORT function
>
> Hello,
>
> I've got interesting case on my 11.2.0.4 .
>
> When using sqlplus
>
> explain plan for delete from SENT where filePath='000103.zip' and filePathHash='ee76f6f'
>
> it is using index which is optimal :
>
> -------------------------------------------------------------------------------------------------------------
>
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
>
> -------------------------------------------------------------------------------------------------------------
>
> | 0 | DELETE STATEMENT | | 1 | 192 | 4 (0)| 00:00:01 |
>
> | 1 | DELETE | SENT | | | | |
>
> |* 2 | TABLE ACCESS BY INDEX ROWID| SENT | 1 | 192 | 4 (0)| 00:00:01 |
>
> |* 3 | INDEX RANGE SCAN | IDXSENT_PATHHASH | 1 | | 3 (0)| 00:00:01 |
>
> -------------------------------------------------------------------------------------------------------------
>
>
>
> PLAN_TABLE_OUTPUT
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
>
> ---------------------------------------------------
>
>
> 2 - filter("FILEPATH"='000103.zip')
>
> 3 - access("FILEPATHHASH"='ee76f6f')
>
>
>
> but when the same query is run by application user there is full table scan run on SENT table and no access predicate, only filter with NLSSORT function like this:
>
> 2 - filter((NLSSORT("FILEPATHHASH",'nls_sort=''BINARY_CI''')=NLSSORT(:2,'nls_sort=''BINARY_CI''')))
>
>
> sqlplus session params:
>
> NLS_SORT = BINARY,
>
> NLS_COMP=BINARY
>
>
> I'm not sure what kind of nls setting application has, but obviously it is something not default and causing the optimizer to deny index access whenever possible,
>
> Any ideas ?
>
> Regards.
>
> Greg
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 02 2019 - 16:39:01 CEST