Re: Query and UPPER function

In article <31D93DA2.4E2A_at_wang.com>, John Moriarty <john.moriarty_at_wang.com> writes:
|> 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.

This is because the UPPER function disables the index. Any function applied to a column will do this (you can verify this by looking at the explain plan).

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

I would either put a trigger on the table which will force last_name and first_name to be uppercase whenever a row is inserted, or add two other columns upper_first_name and upper_last_name which are always in upper case. You can enforce the upper case restriction (whichever field you use) by using a before insert or update row level trigger.

|>
|>
|> thanks for any info,
|>
|>
|> John
 

-- 
------------------------------------------------------------------------
Scott Urman            Oracle Corporation           surman_at_us.oracle.com
------------------------------------------------------------------------
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2
Published by Oracle Press - http://www.osborne.com/oracle/index.htm
------------------------------------------------------------------------
"The opinions expressed here are my own, and are not necessarily that of
 Oracle Corporation"
------------------------------------------------------------------------