Home -> Mailing Lists -> Oracle-L -> Index not used, instead there is filter operation with NLSSORT function

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

From: <grzegorzof_at_interia.pl>
Date: Wed, 02 Oct 2019 11:06:39 +0200
Message-Id: <ecrsyqbtnmhfdmggqhjx_at_lere> 







Hello,&nbsp;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&nbsp; | Operation&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp; &nbsp; &nbsp;|-------------------------------------------------------------------------------------------------------------|&nbsp; &nbsp;0 | DELETE STATEMENT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp;1 |&nbsp; &nbsp;192 |&nbsp; &nbsp; &nbsp;4&nbsp; &nbsp;(0)| 00:00:01 ||&nbsp; &nbsp;1 |&nbsp; DELETE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | SENT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ||*&nbsp; 2 |&nbsp; &nbsp;TABLE ACCESS BY INDEX ROWID| SENT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp;1 |&nbsp; &nbsp;192 |&nbsp; &nbsp; &nbsp;4&nbsp; &nbsp;(0)| 00:00:01 ||*&nbsp; 3 |&nbsp; &nbsp; INDEX RANGE SCAN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | IDXSENT_PATHHASH |&nbsp; &nbsp; &nbsp;1 |&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp;3&nbsp; &nbsp;(0)| 00:00:01 |-------------------------------------------------------------------------------------------------------------PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------&nbsp; &nbsp;2 - filter("FILEPATH"='000103.zip')&nbsp; &nbsp;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



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


Received on Wed Oct 02 2019 - 11:06:39 CEST












Original text of this message