I was asked to review customisations a DBA from another organisation had made to a database when installing a new application. He had decided to deviate from the application vendor’s instructions, which had raised concerns.
The changes that caught my attention were:
alter system set NLS_SORT=BINARY_CI SCOPE=SPFILE; alter system set NLS_COMP=LINGUISTIC SCOPE=SPFILE;
These setting change the way Oracle compares and sorts character strings, specifically to ignore the case (upper or lower).
I remember in the old days (circa Oracle 7) I’d receive complaints about how applications that could run on different databases would be limited by a lack of case-insensitive searches if Oracle was used. I used to suggest that the design could have met this requirement by storing an extra column that could be used for searches. (Eg, all characters converted to uppercase, with spaces, apostrophes and hyphens removed, etc). In more recent versions of Oracle, functional indexes and virtual columns could be considered instead.
While it’s great that Oracle now provides the functionality to allow case-insensitive comparisons and sorting, one should research the feature and understand the ramifications before using it. There is plenty of information about NLS_SORT and NLS_COMP on Metalink, blogs and the documentation, so I’ll be brief.
If the application doesn’t support non-default NLS_SORT and NLS_COMP settings, then they should not be used. The settings in this example can result in indexes not being used, or not being used as intended, because normal indexes are created using a binary sort order. (Not binary_ci). Sorting with values other than BINARY will be slower. As usual, it pays to check for significant bugs before adopting a new feature, especially when deviating from an application vendor’s instructions. In 10gR2 there are bugs that will return incorrect data when using the NLS_COMP & NLS_SORT values as set above. (Eg, bugs 5225005, 5494008).
Metalink note 227335.1 is a good source of further information on this topic.