RE: Issue with NLS_SORT & NLS_COMP

From: Herald ten Dam <>
Date: Thu, 19 Nov 2009 09:58:51 +0100
Message-ID: <BACE06B587FB3C47AC31296417C11434016C13E64A73_at_thnms004.TheHumanNetwork.local>


look at It's the same question. After setting you need to think about indexing the column by using a function based index.

Another solution for case insensitive search is the use of a TEXT-index. This one is normally case insensitive. It requires although a rewrite of the predicate. Have a look at the documentation form oracle:

Herald ten Dam

Van: [] namens Thomas Roach [] Verzonden: woensdag 18 november 2009 18:15 Aan: Oracle Discussion List
Onderwerp: Issue with NLS_SORT & NLS_COMP


I was hoping to get some assistance from the list on solving an interesting problem I am having.

We are trying to have case insensitive searches within a query. In order to do so, development is setting 2 parameters at the session level.

alter session set NLS_SORT=BINARY_CI;
alter session set NLS_COMP=LINGUISTIC;

The query takes a very long time to run, and occassionally stops with the waits 'SQL*Net message to client'. When I ran the query manually, the results returned in less than 5 seconds. When I set NLS_SORT and NLS_COMP the query again hangs and I see the 'SQL*Net message to client' in the wait events. When I set one or the other NLS_COMP or NLS_SORT properties, the query finishes very quickly. In looking at the trace file (10046, level 12), the file size of the one that completes very quickly is roughly 3MB in size. The one that 'hangs' is 7k in size and stuck at 'SQL*Net message to client'.

I am using Oracle RAC on (RHEL 5.3 64-bit) and the client is also at

Any ideas what could be causing this too hang? Any better solutions for supporting case insensitive searches (this is an OBIEE query).

This is the predicate of how they are doing the searches.

COUNTRY_NAME like concat(concat('%', ''), '%') and PROVIDER like concat(concat('%', ''), '%') and CONNECTION_TYPE like concat(concat('%', ''), '%')



Received on Thu Nov 19 2009 - 02:58:51 CST

Original text of this message