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

A copy of this was sent to Ben Ryan <benryan_at_my-deja.com> (if that email address didn't require changing) On Thu, 02 Sep 1999 20:54:46 GMT, you wrote:

>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.
>

Oracle8i, release 8.1 introduced the feature whereby you can INDEX a function (user written function or builtin function).

You can then:

create index myIndex on T ( upper(mycolumn) );

and a select * from T where upper(mycolumn) = 'SOME STRING' will use that index.

See the URL in my signature for a white paper on this feature.

>> ? 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.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 03 1999 - 08:03:07 CDT