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

"Steve Chien" <stevechien@xxxxxxxxxxxxxx> wrote in message
news:s2q8439a2iiofaj35gmlf47t8u7fdj58lg@xxxxxxxxxx
On Fri, 11 May 2007 12:41:24 GMT, "Richard Foote"
<richard.foote@xxxxxxxxxxxxxxxxxx> wrote:

"Cristian Cudizio" <cristian.cudizio@xxxxxxxx> wrote in message
news:1178880528.448552.76480@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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%';


Hi Steve

Unfortunately, you can't perform an *indexed* LIKE search with NLS_COMP set
to LINGUISTIC.

This is clearly documented in the Globalization Support Guide:

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14225/ch5lingsort.htm#i1009059

where it specifically states:

"The SQL functions MAX( ) and MIN( ), and also the LIKE operator, cannot use
linguistic indexes when NLS_COMP is set to LINGUISTIC."

Coincidently, I've recently been approached with the possibility of
presenting a series of seminars on the huge general topic of Oracle Indexing
in Europe sometime later in the year. I'll be sure to include some
information on case-insensitive searches as it's something that appears to
have generated some interest (and disappointments).

Cheers

Richard


.

Relevant Pages