Re: Query and UPPER function

John Moriarty <john.moriarty_at_wang.com> writes:
> ...
> 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.
> ...

When you apply a function to an indexed column, the index cannot be used. This ends up causing a full table scan in most cases.

You have two choices:

1. Make sure you store the data in uppercase only.
2. Store an uppercase 'version' of the data in a separate column, and search on that.

George Mari
Oracle Specialist
Database Software Consulting, Inc.
73220.1277_at_compuserve.com Received on Fri Jul 05 1996 - 00:00:00 CEST