Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 10g2 LIKE operator and case-insensitive issues

Re: Oracle 10g2 LIKE operator and case-insensitive issues

From: Richard Foote <richard.foote_at_bigpond.nospam.com>
Date: Mon, 14 May 2007 11:24:02 GMT
Message-ID: <mFX1i.37859$M.21318@news-server.bigpond.net.au>


"Steve Chien" <stevechien_at_wisagetech.com> wrote in message news:s2q8439a2iiofaj35gmlf47t8u7fdj58lg_at_4ax.com...
> On Fri, 11 May 2007 12:41:24 GMT, "Richard Foote"
> <richard.foote_at_bigpond.nospam.com> wrote:
>
>>"Cristian Cudizio" <cristian.cudizio_at_yahoo.it> wrote in message
>>news:1178880528.448552.76480_at_w5g2000hsg.googlegroups.com...
>>>I have to review my test, there is something wrong, i've observed
>>> strange behaviurs,
>>> but lastli i've a test case.
>>> I'm testing on 10.2.0.2 on Linux suse el x86 64 bit.
>>> Connesso a:
>>> Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
>>>
>>> SQL> drop index idxtestcase;
>>>
>>> Indice eliminato.
>>>
>>> SQL> CREATE INDEX IDXTESTCASE ON AUTENTI (AUTECOGNOME);
>>>
>>> Indice creato.
>>>
>>> SQL> exec
>>> dbms_stats.gather_table_stats(OWNNAME=>'GEOCALL',tabname=>'AUTENTI',CASCADE=>TRUE);
>>>
>>> Procedura PL/SQL completata correttamente.
>>>
>>> SQL> alter Session set nls_comp=linguistic;
>>>
>>> Modificata sessione.
>>>
>>> SQL> alter Session set nls_sort=binary_ci;
>>>
>>> Modificata sessione.
>>>
>>> SQL> alter session set optimizer_mode=first_rows_1;
>>>
>>> Modificata sessione.
>>>
>>> SQL> set autotrace on
>>> SQL> SELECT AUTECOGNOME,ACTION FROM AUTENTI WHERE AUTECOGNOME LIKE
>>> 'cudiz%';
>>>
>>> AUTECOGNOME
>>> --------------------------------------------------------------------------------
>>> ACTION
>>> ----------
>>> CUDIZIO
>>>
>>>
>>>
>>>
>>> Piano di esecuzione
>>> ----------------------------------------------------------
>>> Plan hash value: 3571430138
>>>
>>> -----------------------------------------------------------------------------
>>> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
>>> Time |
>>> -----------------------------------------------------------------------------
>>> | 0 | SELECT STATEMENT | | 1 | 22 | 5 (0)|
>>> 00:00:01 |
>>> |* 1 | TABLE ACCESS FULL| AUTENTI | 1 | 22 | 5 (0)|
>>> 00:00:01 |
>>> -----------------------------------------------------------------------------
>>>
>>> Predicate Information (identified by operation id):
>>> ---------------------------------------------------
>>>
>>> 1 - filter("AUTECOGNOME" LIKE 'cudiz%')
>>>
>>>
>>> Statistiche
>>> ----------------------------------------------------------
>>> 1 recursive calls
>>> 0 db block gets
>>> 16 consistent gets
>>> 0 physical reads
>>> 0 redo size
>>> 402 bytes sent via SQL*Net to client
>>> 338 bytes received via SQL*Net from client
>>> 2 SQL*Net roundtrips to/from client
>>> 0 sorts (memory)
>>> 0 sorts (disk)
>>> 1 rows processed
>>>
>>> SQL> alter session set optimizer_mode=all_rows;
>>>
>>> Modificata sessione.
>>>
>>> SQL> SELECT AUTECOGNOME,ACTION FROM AUTENTI WHERE AUTECOGNOME LIKE
>>> 'cudiz%';
>>>
>>> AUTECOGNOME
>>> --------------------------------------------------------------------------------
>>> ACTION
>>> ----------
>>> CUDIZIO
>>>
>>>
>>>
>>>
>>> Piano di esecuzione
>>> ----------------------------------------------------------
>>> Plan hash value: 3571430138
>>>
>>> -----------------------------------------------------------------------------
>>> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
>>> Time |
>>> -----------------------------------------------------------------------------
>>> | 0 | SELECT STATEMENT | | 1 | 22 | 5 (0)|
>>> 00:00:01 |
>>> |* 1 | TABLE ACCESS FULL| AUTENTI | 1 | 22 | 5 (0)|
>>> 00:00:01 |
>>> -----------------------------------------------------------------------------
>>>
>>> Predicate Information (identified by operation id):
>>> ---------------------------------------------------
>>>
>>> 1 - filter("AUTECOGNOME" LIKE 'cudiz%')
>>>
>>>
>>> Statistiche
>>> ----------------------------------------------------------
>>> 1 recursive calls
>>> 0 db block gets
>>> 16 consistent gets
>>> 0 physical reads
>>> 0 redo size
>>> 402 bytes sent via SQL*Net to client
>>> 338 bytes received via SQL*Net from client
>>> 2 SQL*Net roundtrips to/from client
>>> 0 sorts (memory)
>>> 0 sorts (disk)
>>> 1 rows processed
>>>
>>> SQL> SELECT /*+ INDEX(A IDXTESTCASE) */ AUTECOGNOME,ACTION FROM
>>> AUTENTI A WHERE AUTECOGNOME LIKE 'cu
>>> diz%';
>>>
>>> AUTECOGNOME
>>> --------------------------------------------------------------------------------
>>> ACTION
>>> ----------
>>> CUDIZIO
>>>
>>>
>>>
>>>
>>> Piano di esecuzione
>>> ----------------------------------------------------------
>>> Plan hash value: 3801628502
>>>
>>> --------------------------------------------------------------------------------
>>> -----------
>>>
>>> | Id | Operation | Name | Rows | Bytes |
>>> Cost (%CPU)|
>>> Time |
>>>
>>> --------------------------------------------------------------------------------
>>> -----------
>>>
>>> | 0 | SELECT STATEMENT | | 1 | 22
>>> | 5 (0)|
>>> 00:00:01 |
>>>
>>> | 1 | TABLE ACCESS BY INDEX ROWID| AUTENTI | 1 | 22
>>> | 5 (0)|
>>> 00:00:01 |
>>>
>>> |* 2 | INDEX FULL SCAN | IDXTESTCASE | 1 |
>>> | 3 (0)|
>>> 00:00:01 |
>>>
>>> --------------------------------------------------------------------------------
>>> -----------
>>>
>>>
>>> Predicate Information (identified by operation id):
>>> ---------------------------------------------------
>>>
>>> 2 - filter("AUTECOGNOME" LIKE 'cudiz%')
>>>
>>>
>>> Statistiche
>>> ----------------------------------------------------------
>>> 1 recursive calls
>>> 0 db block gets
>>> 5 consistent gets
>>> 0 physical reads
>>> 0 redo size
>>> 402 bytes sent via SQL*Net to client
>>> 338 bytes received via SQL*Net from client
>>> 2 SQL*Net roundtrips to/from client
>>> 0 sorts (memory)
>>> 0 sorts (disk)
>>> 1 rows processed
>>>
>>> SQL> alter session set optimizer_mode=first_rows;
>>>
>>> Modificata sessione.
>>>
>>> SQL> SELECT AUTECOGNOME,ACTION FROM AUTENTI WHERE AUTECOGNOME LIKE
>>> 'cudiz%';
>>>
>>> AUTECOGNOME
>>> --------------------------------------------------------------------------------
>>> ACTION
>>> ----------
>>> CUDIZIO
>>>
>>>
>>>
>>>
>>> Piano di esecuzione
>>> ----------------------------------------------------------
>>> Plan hash value: 3571430138
>>>
>>> -----------------------------------------------------------------------------
>>> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
>>> Time |
>>> -----------------------------------------------------------------------------
>>> | 0 | SELECT STATEMENT | | 1 | 22 | 5 (0)|
>>> 00:00:01 |
>>> |* 1 | TABLE ACCESS FULL| AUTENTI | 1 | 22 | 5 (0)|
>>> 00:00:01 |
>>> -----------------------------------------------------------------------------
>>>
>>> Predicate Information (identified by operation id):
>>> ---------------------------------------------------
>>>
>>> 1 - filter("AUTECOGNOME" LIKE 'cudiz%')
>>>
>>>
>>> Statistiche
>>> ----------------------------------------------------------
>>> 0 recursive calls
>>> 0 db block gets
>>> 16 consistent gets
>>> 0 physical reads
>>> 0 redo size
>>> 402 bytes sent via SQL*Net to client
>>> 338 bytes received via SQL*Net from client
>>> 2 SQL*Net roundtrips to/from client
>>> 0 sorts (memory)
>>> 0 sorts (disk)
>>> 1 rows processed
>>>
>>>
>>>
>>> Probably i didn't notice when i first made my test that Oracle makes
>>> an INDEX FULL SCAN
>>> so my suggestions were not correct. It make searches case insensitve
>>> but it seems not able to
>>> use the normal indexes.
>>> So i remand on asktom.oracle.com and his suggestions
>>>
>>
>>Hi Cristian / Steve
>>
>>Basic Oracle indexes use binary sort mode and therefore can't be used if
>>nls_sort is not set to binary ...
>>
>>Cheers
>>
>>Richard
>>
> Hi Richard,
>
> How about the information I mentioned in my first POST? I created
> the indexs like,
>
>create index ak2_str1_mytest on mytest(nlssort(str1,
> 'NLS_SORT=GENERIC_M_CI'));
>
> When NLS_COMP=LINGUISTIC & NLS_SORT=GENERIC_M_CI, the index can be
> applied to
>
> select * from mytest where str1 = 'steve';
>
> However, the index was not used for
>
> select * from mytest where str1 like 'steve%';
>

Hi Steve

Unfortunately, you can't perform an *indexed* LIKE search with NLS_COMP set to LINGUISTIC.

This is clearly documented in the Globalization Support Guide:

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14225/ch5lingsort.htm#i1009059

where it specifically states:

"The SQL functions MAX( ) and MIN( ), and also the LIKE operator, cannot use linguistic indexes when NLS_COMP is set to LINGUISTIC."

Coincidently, I've recently been approached with the possibility of presenting a series of seminars on the huge general topic of Oracle Indexing in Europe sometime later in the year. I'll be sure to include some information on case-insensitive searches as it's something that appears to have generated some interest (and disappointments).

Cheers

Richard Received on Mon May 14 2007 - 06:24:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US