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

On 11 May 2007 03:48:48 -0700, Cristian Cudizio
<cristian.cudizio@xxxxxxxx> 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
.

Relevant Pages