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

On 11 May 2007 03:48:48 -0700, Cristian Cudizio <cristian.cudizio_at_yahoo.it> wrote:

>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
>
>Bye
>Cristian Cudizio
>
>http://oracledb.wordpress.com
>http://cristiancudizio.wordpress.com
>

  Thanks for your time. I'll see if anyone can help on the NEWSGROUP and try to post it to ASKTOM.

• Steve