Collation and Sorting

Oracle issues

Oracle has offered language-dependent sorting for some time. In practice, you could not use this sorting option.

The major flaw in Oracle’s sorting architecture was that the sorting operation was carried out on the result set, while the database engine was completely unaware of the sorting.

Example (NLS_SORT=german):

Table vendor:
	                               Name	   id
	                               übels	  10
	                               conn	   20
	                               selmer	 30
	                               willson	40
SQL> select *
from vendor
order by name;
	                               Name	   id
	                               conn	   20
                               	selmer	 30
                               	übels	  10
                               	willson	40
SQL> select *
from vendor
where name between ‘a’ and ‘z’
order by name;
                               	Name	   id
                               	conn	   20
                               	selmer	 30
                               	willson	40

The second query is missing the record ‘übels’, which the database engine rejected because ü is binary larger than z.

From Oracle 9i onwards, Oracle offers functionality such as functional indexes and the nls_comp parameter that solve this problem.

For more information, see the Oracle documentation, such as the Oracle 11g Database Globalization Support Guide, Release 1 (B28298-02)

LN porting set

Another essential requirement is that the sorting order in the porting set must be identical to the sorting order in the underlying database.

If the porting set uses a sorting order other than the RDBMS, undefined behavior occurs; this implies you cannot use all sorting and collation orders offered by the RDBMS vendor.

Supported sorting orders

Currently, only these sorting and collation orders are supported:

• binary:

  This order must be used if the porting set Single byte and Multibyte setups.

• infor_generic_m:

  This order is used if the porting set runs in Unicode mode. This collation is only supported for Oracle versions up to Oracle 11.2.

• UCA0610_DUCET_S3_VN_BN_NY_EN_FN_HN_DN_MN:

  This order is used if the porting set runs in Unicode mode for Oracle 12.1.

• UCA0700_DUCET_VN

  This order is used if the porting set runs in Unicode mode for Oracle 12.2 onwards.

Other sorting and collation orders might be added in future releases.

Configuration

This resource variable nls_sort affects the sorting of the result sets returned from the Oracle RDBMS. In theory, the variable can differ by Oracle session. In practice, this is not possible, because the table indexes must be created for this sorting order to have a reasonable performance.

The nls_sort variable is set in the $BSE/lib/defaults/db_resource file by the LN installer.

If you change the value of nls_sort, all table indexes created by the oracle driver must be rebuilt by a complete reorganize tables operation from the LN tools.