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

  • From: grzegorzof@xxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 02 Oct 2019 11:06:39 +0200

Hello, I've got interesting case on my 11.2.0.4 .When using sqlplusexplain 
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=BINARYI'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

Other related posts: