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

Steve Chien wrote:
> It did work with the "=", but not the "LIKE".
>
> - Steve

But you can explicitly do that. Of course it means you have to modify your application code (if it has not been done from the very begginning):

SQL> create table t (name varchar2(100));

Table created.

SQL> insert into t select object_name from dba_objects;

55640 rows created.

SQL> create index t_idx on t (upper(name));

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 't')

PL/SQL procedure successfully completed.

SQL> set autot on
SQL> set autot traceonly
SQL> select * from t where upper(name) like 'CONT%';

11 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2 Bytes=48)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=3 Card=

          2 Bytes=48)

   2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=2 Card=2)

Or the same with variables:

SQL> variable x varchar2(100)
SQL> begin
  2 :x := 'cont';
  3 end;
  4 /

PL/SQL procedure successfully completed.

SQL> select * from t where upper(name) like upper(:x)||'%';

11 rows selected.

Execution Plan

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=54 Card=2782 Bytes
          =66768)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=54 Card
          =2782 Bytes=66768)

   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=4 Card=501)

Gints Plivna
http://www.gplivna.eu Received on Sat May 12 2007 - 01:42:21 CDT