Re: Index not used, instead there is filter operation with NLSSORT function
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 2 Oct 2019 14:39:01 +0000
what is surprising for me, in the sql plan from client app there is no
HEXTORAW function.
Reminder, client app plan filter operation is:
2 -
filter((NLSSORT("FILEPATHHASH",'nls_sort=''BINARY_CI''')=NLSSORT(:2,'nls_sort=''BINARY_CI''')))
The difference is that the client is sending in a bind variable, your example
is using a literal, so the optimizer knows that it can derive the constant that
is the result of nlssort() to your input.
If you modelled in SQL*Plus by
variable mv varchar2(10)
exec :mv := 'ee76f6f';
Then queried for filepathhash = :mv
you'd see the nlssort() applied to the bind variable rather than the hextoraw()
literal value.
Regards
Jonathan Lewis
________________________________________
From: grzegorzof@xxxxxxxxxx <grzegorzof@xxxxxxxxxx>
Sent: 02 October 2019 13:17
To: Jonathan Lewis
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Index not used, instead there is filter operation with NLSSORT
function
Thanks for all the answers, indeed after setting:
alter session set nls_sort = binary_ci;
and below was neccessary
alter session set nls_comp = linguistic;
Oracle is not able to use index anymore and filter predicate for full table
scan looks like this:
2 - filter(NLSSORT("FILEPATHHASH",'nls_sort=''BINARY_CI''')=HEXTORAW('66') AND
NLSSORT("FILEPATH",'nls_sort=''BINARY_CI''')=HEXTORAW('97') )
I've trimmed hextoraw values to make response clear, what is surprising for me,
in the sql plan from client app there is no HEXTORAW function.
I've checked the bind values and it was of type :
VARCHAR2(2000)
the column itself is :
FILEPATHHASH NOT NULL VARCHAR2(40 CHAR)
Reminder, client app plan filter operation is:
2 -
filter((NLSSORT("FILEPATHHASH",'nls_sort=''BINARY_CI''')=NLSSORT(:2,'nls_sort=''BINARY_CI''')))
Regards.
Greg
Od: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
Do: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>;
Wysłane: 11:23 Środa 2019-10-02
Temat: Re: Index not used, instead there is filter operation with NLSSORT
function
I thought I had an example of this on my blog somewhere, but all I could find
was a note about "nls based" indexes not behaving as nicely as they should -
but there may be something there that you find useful:
https://jonathanlewis.wordpress.com/2016/01/06/nls-mess/
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Jonathan Lewis
Sent: 02 October 2019 10:16
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Index not used, instead there is filter operation with NLSSORT
function
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@xxxxxxxxxxxxx on behalf of grzegorzof@xxxxxxxxxx
Sent: 02 October 2019 10:06
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
