Re: Case Insensitivity

Kevin,

You could also use OMNIDEX, an advanced indexing technology that layers on top of Oracle. See http://www.disc.com/keywords.html or contact me.

Cheryl Grandy
cgrandy_at_disc.com
303 444-4000
www.disc.com/home

In article <DjQj5.71717$3E6.720890_at_news1.alsv1.occa.home.com>,   "Kevin Lang" <KLang_at_kevinlang.com> 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
>
>

--
Cheryl Grandy
DISC
Get OMNIDEX for the fastest
applications ever


Sent via Deja.com http://www.deja.com/
Before you buy.