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

On Mon, 14 May 2007 11:24:02 GMT, "Richard Foote" <richard.foote_at_bigpond.nospam.com> wrote:

>"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
>

Richard,

  Thanks for the information and good luck!

• Steve