Re: Case Insensitivity

Kevin Lang wrote:
>
> Hello,
>
> How is case insensitivity emulated in Oracle?
> I see that there are function-based indexes that would allow me to index a
> mixed-case column as UPPER(colname), and then use the "UPPER" function in
> the predicate, but I can not get the optimizer to use that index. A table
> scan is always generated. I have analyzed the table and indexes updating
> the statistics, there are no NULLS, all of the rules are followed.
>
> CREATE TABLE TABLE1 (COL1 number, COL2 varchar2(50) )
> CREATE INDEX INDEX1 on TABLE1 UPPER(COL2) -- syntax off the top of my
> head - may not be perfect.
> ANALYZE TABLE1 COMPUTE STATISTICS ""
> ""
> EXPLAIN PLAN SET STATEMENT_ID = '1' for Select COL1 from TABLE1 where
> UPPER(COL2) = UPPER :VARIABLE1
>
> There are over 100,000 rows in the table, and a variety of syntactical
> variations of the predicate have been tried, but the index is never
> selected.
>
> Is there anybody else implementing ad-hoc searching of mixed-case character
> columns?
>
> Thank You,
>
> Kevin Lang
> KLang_at_kore.com

There is an article on my web site which discusses what you need to enable function based indexes..

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse