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

"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 Received on Fri May 11 2007 - 07:41:24 CDT