ORACLE - How to create indexes to be used when NLS_COMP = Linguistic and NLS_Sort = Binary_CI

Question

By default, Oracle uses indexes created.

When I go to NLS_COMP = Linguistic and NLS_Sort = Binary_CI, I get a full table scan.

I read somewhere creating an index using (nlssort (name, 'NLS_SORT = BINARY_CI')); It should work.

As my attempt below shows, not so much. Even if I force it, the performance does not seem to be what I would expect. This is a trivial example that I like to solve for a table with many millions of rows, so a full table scan would be bad.

So the question is how to create indexes so that they are used.

thanks

- X setting

create table x ( name varchar2(30)) ;
insert into x select table_name from all_tables;
create index x_ix on x (name);
create index x_ic on x (nlssort(name, 'NLS_SORT=BINARY_CI'));
/   

- Default settings

ALTER SESSION SET NLS_COMP=BINARY;
ALTER SESSION SET NLS_SORT=BINARY; 
/
set autotrace on 
/
select * from X where NAME like 'x%';

--0 rows selected
--
---------------------------------------------------------------------------
--| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
--|   0 | SELECT STATEMENT |      |     1 |    17 |     1   (0)| 00:00:01 |
--|*  1 |  INDEX RANGE SCAN| X_IX |     1 |    17 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
/
set autotrace off
/

- Linguistic

ALTER SESSION SET NLS_COMP=LINGUISTIC; 
ALTER SESSION SET NLS_SORT=BINARY_CI; 
/
set autotrace on
/
select * from X where NAME like 'x%';
--13 rows selected
--
----------------------------------------------------------------------------
--| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
--|   0 | SELECT STATEMENT  |      |     1 |    17 |     3   (0)| 00:00:01 |
--|*  1 |  TABLE ACCESS FULL| X    |     1 |    17 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

select /*+ INDEX( X  X_IX           ) */ * from X where   NAME like 'x%';
--13 rows selected
--
---------------------------------------------------------------------------
--| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
--|   0 | SELECT STATEMENT |      |     1 |    17 |     9   (0)| 00:00:01 |
--|*  1 |  INDEX FULL SCAN | X_IX |     1 |    17 |     9   (0)| 00:00:01 |
---------------------------------------------------------------------------

select /*+ INDEX( X  X_IC           ) */ * from X where   NAME like 'x%'; 
--13 rows selected
--
--------------------------------------------------------------------------------------
--| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
--|   0 | SELECT STATEMENT            |      |     1 |    17 |   448   (1)| 00:00:06 |
--|*  1 |  TABLE ACCESS BY INDEX ROWID| X    |     1 |    17 |   448   (1)| 00:00:06 |
--|   2 |   INDEX FULL SCAN           | X_IC |  1629 |       |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
/

set autotrace off
/

Answer 1

Oracle 11g - LIKE CAN . :

The SQL functions MAX( ) and MIN( ) cannot use linguistic indexes when NLS_COMP is set to LINGUISTIC

, ", LIKE".

Answer 2

(10.2.0.3). , LIKE - 10gR2

SQL MAX() MIN(), LIKE, , NLS_COMP LINGUISTIC.

, - SORT.

- , UPPER(name) UPPER(name) LIKE UPPER('x%').

( ) , Oracle Text indexes.

: : LIKE 'ABC%' :

SQL> select * from x where name >= 'ABC' and name < 'ABD';

Execution Plan
----------------------------------------------------------
Plan hash value: 708878862

--------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time  
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    24 |     4   (0)| 00:00:
|   1 |  TABLE ACCESS BY INDEX ROWID| X    |     1 |    24 |     4   (0)| 00:00:
|*  2 |   INDEX RANGE SCAN          | X_IC |     1 |       |     3   (0)| 00:00:
--------------------------------------------------------------------------------

, LIKE (> <), .