Initialization Parameters, 112 of 188

NLS_SORT

Parameter type:
String

Syntax:
NLS_SORT = {BINARY | linguistic_definition}

Parameter class:
Dynamic. Scope = ALTER SESSION.

Default value:
Derived from NLS_LANGUAGE

Range of values:
BINARY or any valid linguistic definition name

NLS_SORT specifies the collating sequence for ORDER BY queries.

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.

Note: Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. BINARY is the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the ORDER BY clause when NLS_SORT is set to BINARY. If NLS_SORT is 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.
See Also:

"Linguistic definitions" in Oracle8i National Language Support Guide for a current listing of values you can specify for this parameter.

Oracle8i Concepts for more information on this parameter.

Parameter type:	String
Syntax:	`NLS_SORT = {BINARY \| linguistic_definition}`
Parameter class:	Dynamic. Scope = `ALTER` `SESSION.`
Default value:	Derived from `NLS_LANGUAGE`
Range of values:	`BINARY` or any valid linguistic definition name
`NLS_SORT` specifies the collating sequence for `ORDER` `BY` queries. 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.
Note: Setting `NLS_SORT` to anything other than `BINARY` causes a sort to use a full table scan, regardless of the path chosen by the optimizer. `BINARY` is the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the `ORDER` `BY` clause when `NLS_SORT` is set to `BINARY`. If `NLS_SORT` is 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. See Also: "Linguistic definitions" in Oracle8i National Language Support Guide for a current listing of values you can specify for this parameter. Oracle8i Concepts for more information on this parameter.