StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POORACLE - How do I create indexes that will be used when NLS_COMP=Linguistic and NLS_Sort=Binary_CI
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload