Making Oracle sorting case-insensitive?

Question

This is related to making oracle sorting case-insensitive. Most of the solutions I have seen mentions setting below session params :

ALTER SESSION SET NLS_COMP = LINGUISTIC;
ALTER SESSION SET NLS_SORT = BINARY_CI;

By default NLS_COMP is BINARY.

I found that if I just set NLS_SORT to BINARY_CI without setting the NLS_COMP to LINGUISTIC, it still works, i.e oracle sort becomes case-insensitive. Is there any advantage to setting NLS_COMP param ?

Answer 1

NLS_COMP and NLS_SORT have slightly different effects. NLS_COMP is, as the name implies, for comparisons. NLS_SORT is, as the name implies, for sorting. Setting NLS_COMP to LINGUISTIC causes comparisons to follow the sorting rules, causes comparisons to use the NLS_SORT setting. You can see the difference when you try:

SELECT 1 FROM DUAL WHERE 'A' = 'a';

With NLS_COMP = BINARY, the comparison gives false. With NLS_COMP = LINGUISTIC and NLS_SORT = BINARY_CI, the comparison gives true.

Whether you should set that depends on what results you want to get from your queries.