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

From: Cristian Cudizio <cristian.cudizio_at_yahoo.it>
Date: 11 May 2007 03:48:48 -0700
Message-ID: <1178880528.448552.76480@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

Bye
Cristian Cudizio

http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com Received on Fri May 11 2007 - 05:48:48 CDT