Re: nls_sort on index definition causes index not to be used

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 22 Feb 2017 18:30:14 +0000
Message-ID: <MMXP123MB0847E13A481E125778CD0140A5500_at_MMXP123MB0847.GBRP123.PROD.OUTLOOK.COM>

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_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

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 - 19:30:14 CET