Re: nls_sort on index definition causes index not to be used
Date: Wed, 22 Feb 2017 13:33:13 -0500
Message-Id: <58AD93190200000B000209CF_at_groupwise2014.gcrta.org>
I had to set:
NLS_COMP = LINGUISTIC
NLS_SORT = PUNCTUATION
Then it worked.
>>> Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> 2/22/17 1:30 PM >>>
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
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
Predicate Information (identified by operation id):
2 - access(NLSSORT(TO_CHAR("SAL"),'nls_sort=''PUNCTUATION''')=HEXTORAW('A
1A1A0A0000101010100') )
Regards
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
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 |
--------------------------------------------------------------------------
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 |
------------------------------------------------------------------------------------
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Beckstrom Jeffrey <JBECKSTROM_at_gcrta.org>
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
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?
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 22 2017 - 19:33:13 CET