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 09:16:26 +0000
Message-ID: <CWLP265MB1748236FB63A1817005FF999A59C0_at_CWLP265MB1748.GBRP265.PROD.OUTLOOK.COM>


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 <oracle-l-bounce_at_freelists.org> on behalf of grzegorzof_at_interia.pl <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
Received on Wed Oct 02 2019 - 11:16:26 CEST

Original text of this message