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

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Fri, 03 Sep 1999 13:44:02 GMT
Message-ID: <37d1cf7b.3128869@netnews.worldnet.att.net>

If you are using Oracle8i, look into creating a function-based index. You can actually create the index on on UPPER(charcolumn). It's pretty cool.

If you aren't at 8i yet, there is an approach that you can use. First, alter the table and add a new column:

	alter table xyz 
		add (charcolumn_upper   varchar2(999));

Replace 999 with whatever your length is. Next create a trigger, like so:

create or replace trigger set_shadow_field before insert or update on xyz
for each row
begin

        charcolumn_upper := upper(charcolumn); end;
/

After creating the trigger (or maybe before), issue an update on the table like this:         

	update xyz
		set charcolumn_upper = upper(charcolumn);

Lastly, and this is the hard part, modify your software to use the upper case version of the column in the query's where clause. For example:

>SELECT abc from yxz
>WHERE charcolumn_upper =: myparam
>(myparam comes from a delphi program)

It's the UPPER function applied to the column that negates the use of an index. This is kind of a convoluted way around the issue, but it works.

regards,

Jonathan

---

jonathan_at_gennick.com
http://gennick.com
Brighten the Corner Where You Are

On Thu, 2 Sep 1999 19:18:54 +0200, "Michael Keppler" <Michael.Keppler_at_bigfoot.com> wrote:

>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
>?
Received on Fri Sep 03 1999 - 08:44:02 CDT