|Default value||Derived from
|Range of values||
If the value is
BINARY, then the collating sequence for
ORDER BY queries is based on the numeric value of characters (a binary sort that requires less system overhead).
If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (but not all) languages supported by the
NLS_LANGUAGE parameter also support a linguistic sort with the same name.
NLS_SORTto anything other than
BINARYcauses a sort to use a full table scan, regardless of the path chosen by the optimizer.
BINARYis the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the
ORDER BYclause when
NLS_SORTis set to
NLS_SORTis set to any linguistic sort, the optimizer must include a full table scan and a full sort in the execution plan.
You must use the
NLS_SORT operator with comparison operations if you want the linguistic sort behavior.