Looks like the application is trying to apply a case insensitive match. Is
that intentional? Probably not against a hash column, sounds reasonable for
a file path though.
Hope this gets you started,
Andy
On Wed, 2 Oct 2019 at 10:07, <grzegorzof@xxxxxxxxxx> wrote:
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
