Re: Query and UPPER function

On Tue, 02 Jul 1996 15:17:54 +0000, John Moriarty <john.moriarty_at_wang.com> wrote:

>I attempted the following query in a table that had approx.
>300,000 rows. All queries are performed on a HP-UX server.
>
>Note: An index is applied to the "last_name" column.
>
>Select FIRST_NAME,LAST_NAME,MIDDLE_NAME
>From patient
>Where LAST_NAME LIKE 'ANDERSON%'
>and FIRST_NAME LIKE 'HAROLD%'
>Order By LAST_NAME;
>
> The query comes back almost immediately and returns a single
>row.
>
>
> I then attempted the following query and used the UPPER
>function.
>
>Select FIRST_NAME,LAST_NAME,MIDDLE_NAME
>From patient
>Where UPPER(LAST_NAME) LIKE 'ANDERSON%'
>and UPPER(FIRST_NAME) LIKE 'HAROLD%'
>Order By LAST_NAME;
>
>
> This query took about 6 minutes to come back. It seems to be
>going through all the rows and doing the comparison.
>
>
> Since I can't make the assumption that the Last/First Name
>is going to be all uppercase, does anyone know of a way to speed
>up the second query?
>
>
> thanks for any info,
>
>
> John
UPDATE MY_TABLE

	SET LAST_NAME = UPPER(LAST_NAME),
	SET FIRST_NAME=UPPER(FIRST_NAME);

it'll take 6 minutes, but only once
 :)
Regards
Dave Received on Wed Jul 03 1996 - 00:00:00 CEST