RE: Issue with NLS_SORT & NLS_COMP

Hi,

look at 
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:16370675423662.
 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: 
http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/toc.htm

Herald ten Dam
Superconsult

________________________________
Van: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] namens 
Thomas Roach [troach@xxxxxxxxx]
Verzonden: woensdag 18 november 2009 18:15
Aan: Oracle Discussion List
Onderwerp: Issue with NLS_SORT & NLS_COMP

Hi,

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 10.2.0.4 (RHEL 5.3 64-bit) and the client is also at 
10.2.0.4.

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('%', ''), 
'%')

Thanks,

Tom

Other related posts: