Re: Query and UPPER function
Date: 1996/07/02
Message-ID: <4rburr$ae3_at_inet-nntp-gw-1.us.oracle.com>#1/1
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.
|>
|>
|> 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
-- ------------------------------------------------------------------------ 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" ------------------------------------------------------------------------Received on Tue Jul 02 1996 - 00:00:00 CEST