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

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%';

  Any suggestion?

• Steve