Oracle Parameter NLS_COMP

As mentioned in a former article, sorting sequences of chars and numbers may differ. A binary (ascii based) sort produces a 0-9, A-Z sequence while a linguistic sort (e.g. german) gives A-Z, 0-9. in an order by clause this can be hardcoded directly via the usage of the nlssort function. e.g.

select col1 from ... order by nlssort( col1, 'NLS_SORT=GERMAN') desc

Controlling this can also be done using database parameters or session scoped parameters so that no nlssort function must be applied.

Imagine an application allows browsing forward and backward in an ordered result list. Further, assume this result is presented using a linguistic sort sequence (e.g. GERMAN, see above). With this, it is likely that this result is done with the help of character comparisons inside a where clause.

select shortname, description,id from

(

  select t1.shortname, t2.description, t1.id 

  from t1 natural join t2

  where t1.shortname > 'Xeno'

  order by nlssort( t1.col1, 'NLS_SORT=GERMAN') asc

)

where rownum < 13

Assume this statement presents the last page after some browsing in a forward direction has been done. Because of the german sort, it is expected that shortnames starting with numbers are presented within the last pages. For this to work the comparison operator has to know whether characters containing numbers are greater then 'Z' or not. This depends on the value of initialazion parameter NLS_COMP. With BINARY as default value '9'<'A' is true.

SQL> -- default NLS_COMP is BINARY

SQL> select * from dual where '9'<'A';



D

-

X



SQL> alter session set NLS_COMP=ANSI;

...

SQL> select * from dual where '9'<'A';



no rows selected



SQL> select * from dual where '9'>'Z';



D

-

X

Within this issue there are some side effects and further considerations.
Index (ordinary B* indexes) storage is always BINARY. i found no way to create an index with an ANSI leaf-storage sequence . With this in mind it is possible to implement a paging of results without using inline views as above. Its possible to use INDEX_ASC or INDEX_DESC optimizer hints and thus to omit an order by clause. This implements an ISAM access using ordinary oriented tables. (for ISAM on index oriented tables (IOT) without an order by clause the column for which ordering and restrictions occur has to be the primary key). This works only if a binary sorting (NLS_SORT==NLS_COMP==BINARY) is wanted.

As shown above, its possible to apply nlssort() for ANSI sorting at the statement level. this produces no side effects. But as shown, results may not be correct. For the comparision operator, this fine grained behaviour cannot be achieved. if the session scoped value of NLS_COMP is set to ANSI all SQL statements (in your session) may be affected with this change. This can lead to severe performance impacts for an application. As an index traversal access path is no longer possible (leaf storage is binary but we are doing an ANSI-sort-like restriction in the where clause) full scans occur. This is getting even more worse when an application is using an connection pooling.

when switching to an ANSI sort, its possible to workaround this at the statement level. For a forward scroll:

  ...

  where ( t1.shortname > 'Xeno' or t1.shortname <= '9' )

  order by nlssort( t1.col1, 'NLS_SORT=GERMAN') asc

  ...

Shortnames characters beginning with numbers are included (because of or ...) and the usage of nlssort puts them to the rear end of the result list. For a backward scroll numbers has to be suppressed:

  ...

  where ( t1.shortname < 'Ceno' and t1.shortname >= 'A' )

  order by nlssort( t1.col1, 'NLS_SORT=GERMAN') asc

  ...