Database Reference
In-Depth Information
SQL> CREATE INDEX i_c1 ON t (c1);
SQL> ALTER SESSION SET nls_sort = generic_m_ai;
SQL> ALTER SESSION SET nls_comp =
binary
;
SQL> SELECT /*+ index(t) */ * FROM t WHERE c1 = 'LEON';
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 |
INDEX RANGE SCAN
| I_C1 |
--------------------------------------------
2 - access("C1"='LEON')
SQL> ALTER SESSION SET nls_comp =
linguistic
;
SQL> SELECT /*+ index(t) */ * FROM t WHERE c1 = 'LEON';
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 |
TABLE ACCESS FULL
| T |
----------------------------------
1 - filter(NLSSORT("C1",'nls_sort=''GENERIC_M_AI''')=HEXTORAW('022601FE02380232') )
Obviously, when the
nls_comp
initialization parameter is set to
linguistic
, the index is no longer used. The
reason is indicated by the last line of the output. No lookup in the index is possible because a function,
nlssort
, is
silently applied to the indexed column
c1
. Hence, for that purpose, a function-based index is necessary to avoid a
full table scan. It's essential to recognize that the definition of the index must contain the same value as the
nls_sort
initialization parameter. Therefore, if several languages are in use, several indexes ought to be created:
SQL> CREATE INDEX i_c1_linguistic ON t (
nlssort(c1,'nls_sort=generic_m_ai')
);
SQL> SELECT /*+ index(t) */ * FROM t WHERE c1 = 'LEON';
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 |
INDEX RANGE SCAN
| I_C1_LINGUISTIC |
-------------------------------------------------------
2 - access(NLSSORT("C1",'nls_sort=''GENERIC_M_AI''')=HEXTORAW('022601FE02380232') )
