Consultor Eletrônico

Status: Unverified

GOAL:

How to set NLS_SORT for Oracle

GOAL:

Example of Oracle linguistic index usage

FIX:

Setting NLS_SORT to BINARY is the easy way to improve performance for any ORDER BY; this is unfortunately making the order no more according to your language.

Starting with Oracle 8i, the solution is to use LINGUISTIC INDEXES.

An index can be created with a specific collating sequence.
For GERMAN collation for example, you'll create the index with:
CREATE INDEX emp4_i1 ON emp4(NLSSORT(ename, 'NLS_SORT=German'));
If the session NLS_SORT is German, and a query on this table using emp4_i1
would return the data order by ename and in sort order German collating
sequence would be used.

For the linguistic search to be used instead of Binary search, set the session parameter NLS_COMP=ANSI:
ALTER SESSION SET NLS_COMP=ANSI;

Change the language to GERMAN.
ALTER SESSION SET NLS_LANGUAGE=GERMAN;

For the query like
SELECT ename FROM emp4 WHERE ename = 'MIKE' ORDER BY ename;

the optimizer plan is as shown below:

QUERY_PLAN
--------------------------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID EMP4
INDEX RANGE SCAN EMP4_I1