Re: index to speed up UPPER(column) ??

In article <7qmb76$tcr_at_TGZ3>,
  "Michael Keppler" <Michael.Keppler_at_bigfoot.com> wrote:
> Hello everybody !
>
> In some of our queries it's import to find data wether it is uppercase
> or lowercase. If I define an index on the column with the chars I
don't
> get any speedup if I do
> SELECT abc from yxz
> WHERE UPPER(charcolumn)=:myparam
> (myparam comes from a delphi program)
>
> Is there a way to define some kind of index, that will speedup the
query

My understanding is that ANY function around the column name in your where clause prevents the index being used, causing a table scan instead. If you had enough room to store a duplicate of your charcolumn in a second column so the duplicate data was always in, say, uppercase and you indexed this new column then you could write

WHERE newcharcolumn = UPPER(:myparam)

which would use the index.

Maybe Oracle8i has replicated the case insensitive search capability found MS-SQL, so you would not need this workaround. That is for someone else to answer.

> ? Or do I have another possibility to get a fast selection that
doesn't
> make differences between uppercase, lowercase and mixtures of them (I
> think in SQL-Server it is an installation option)?
>
> Ciao and Thanks, Michael.
>
> --
> Michael Keppler, MCSE
> IT logic GbR
> Michael.Keppler_at_gmx.de
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 02 1999 - 15:54:46 CDT