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

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Wed, 22 Feb 2017 20:16:17 +0100
Message-ID: <CALH8A92iHnS2koUeYaYLLHwBJyrAwh0m45BicU7LP4HMFDYSjg_at_mail.gmail.com>



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_at_jlcomp.demon.co.uk>:

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

-- 
Martin Berger         +43 660 2978929 <+436602978929>
martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
^∆x      http://berxblog.blogspot.com

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 22 2017 - 20:16:17 CET

Original text of this message