nls_sort on index definition causes index not to be used

From: Beckstrom Jeffrey <JBECKSTROM_at_gcrta.org>
Date: Wed, 22 Feb 2017 12:47:25 -0500
Message-Id: <58AD885D0200000B000209B9_at_groupwise2014.gcrta.org>

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?

--

http://www.freelists.org/webpage/oracle-l Received on Wed Feb 22 2017 - 18:47:25 CET