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

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 2 Oct 2019 09:21:53 +0000


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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf 
of Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
Sent: 02 October 2019 10:16
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf 
of grzegorzof@xxxxxxxxxx <grzegorzof@xxxxxxxxxx>
Sent: 02 October 2019 10:06
To: oracle-l@xxxxxxxxxxxxx
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




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


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


Other related posts: