I was sceptical about nls_sort=punctuation on numbers as well, but there
seems to be a difference:
https://livesql.oracle.com/apex/livesql/file/content_EL52ZTYR9GABVT909FLUJJZ8A.html
(even I can't imagine a business case for this ...)
Martin
2017-02-22 19:30 GMT+01:00 Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>:
The problem is the nlssort() - you've got function-based indexes and
aren't applying the same function when you query the data.
Is employee_id a character type ? "_ID" commonly indicates numeric and it
looks really strange to apply nlssort() to a number:
Example of workaround - old scott.emp table:
SQL> create index e_i1 on emp(nlssort(sal,'nls_sort=''punctuation'''));
set autotrace traceonly explain:
SQL> select * from emp where sal = 1100;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 18 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 18 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"=1100)
SQL> select * from emp where nlssort(sal,'nls_sort=''punctuation''') =
nlssort(1100,'nls_sort=''punctuation''');
Execution Plan
----------------------------------------------------------
Plan hash value: 2872601085
------------------------------------------------------------
------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------
------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | E_I1 | 1 | | 1 (0)|
00:00:01 |
------------------------------------------------------------
------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(NLSSORT(TO_CHAR("SAL"),'nls_sort=''PUNCTUATION''')=
HEXTORAW('A
1A1A0A0000101010100') )
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Beckstrom Jeffrey <JBECKSTROM@xxxxxxxxx>
Sent: 22 February 2017 17:47:25
To: oracle-l-freelists; oracle-db-l
Subject: nls_sort on index definition causes index not to be used
We have a 3rd party application for which indexes are built with
nls_sort=punctuation on some of the columns. From SQL Plus a simple select
on the column is using a full table scan. With a hint, it uses a full scan
of the index. I presume the problem is the nls_sort. Example:
U
N prefix
INDEX_NAME OWNER Q length COLUMN_POSITION COLUMN_NAME COLUMN_EXPRESSION
------------------------------ --------- - ---------- ---------------
------------------------------ --------------------
EMPLOYEE_IDX1 xxxxxx Y 1 SYS_NC00042$ NLSSORT("EMPLOYEE_ID
",'nls_sort=''PUNCTU
ATION''')
Can someone tell me why?
--
//www.freelists.org/webpage/oracle-l
