Re: Index not used, instead there is filter operation with NLSSORT function
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-lReceived on Wed Oct 02 2019 - 11:16:26 CEST