title: ORACLE - How do I create indexes that will be used when NLS_COMP=Linguistic and NLS_Sort=Binary_CI
body: <p>By default Oracle uses indexes created.</p>
  
  <p>When I change to NLS_COMP=Linguistic and NLS_Sort=Binary_CI, I get full table scans. </p>
  
  <p>I'd read somewhere that creating an index using (nlssort(name, 'NLS_SORT=BINARY_CI'));
  Would work. </p>
  
  <p>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 I like to solve this for a table with many millions of rows, so full table scans would be bad.</p>
  
  <p>So the question is how to I build indexes so they will be used.</p>
  
  <p>Thanks</p>
  
  <p>--  Setup X</p>
  
  <pre><code>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'));
  /   
  </code></pre>
  
  <p>--  Default Settings</p>
  
  <pre><code>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
  /
  </code></pre>
  
  <p>--  Linguistic</p>
  
  <pre><code>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
  /
  </code></pre>