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

On 11 May 2007 23:42:21 -0700, Gints Plivna <gints.plivna_at_gmail.com> wrote:

>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

  Initially, the application was developed against MS SQLServer, so we didn't have the "upper" in place. That's what we're trying to avoid...

  If that's the last resort, we might really need to do so... Really wonder why Oracle 10g couldn't deal this kind of issues with NLS completely...

  Tks!

• Steve