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

From: Andy Sayer <andysayer_at_gmail.com>
Date: Wed, 2 Oct 2019 10:11:56 +0100
Message-ID: <CACj1VR5pMBKbdHWJvKve3dDjwj1wBc6g53EEc-Pg1=i=31KzAQ_at_mail.gmail.com>



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_at_interia.pl> 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
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 02 2019 - 11:11:56 CEST

Original text of this message