Re: Case Insensitive Query Performance

From: Thomas Sommerfeld <Thomas.Sommerfeld_at_Baden-Online.de>
Date: Thu, 16 Apr 1998 23:17:37 +0200
Message-ID: <35367570.9879221F@Baden-Online.de>

The problem is that Oracle isn't able to use an index for a column, if you use a function in the where clause for this col. With a little trick, you enable Oracle to use an index to select a relatively small number of possible reocord and then do a full scan on these small number.

Try the following statement: (e.g. you want to select records with surname 'NEWTON')
SELECT ...
FROM xyz

WHERE (       ( surname  LIKE 'ne%'  )
                OR ( surname LIKE 'Ne%'  )
                OR ( surname LIKE 'NE%' )
                OR ( surname like 'nE%' )
               )

  AND UPPER(surname) = 'NEWTON' ;

So with the first part of the query, Oracle can use an index on surname and for the second part it has to scan only a few records, instead of more than 400K. (If the performance is still poor, try all lower/upper combinations of the first three letters of the search criteria)

Hope this helps.

Dean Mah wrote:

> This must be a FAQ....
>
> I have a table with a surname field which is mixed case. I want to do
> a
> case insensitive search against this field. I have used the UPPER
> function to force both the surname and my search criteria to
> uppercase.
> Obviously, this results in poor performance.
>
> I have tried to create an index with UPPER(surname) which didn't work.
>
> I've tried to create a view with UPPER(surname) which didn't work. I
> don't want to create another field with the surname in uppercase
> because
> there are >400K records in the table and it is growing.
>
> Any hints, ideas, solutions, ... would be greatly appreciated.
> Dean

Received on Thu Apr 16 1998 - 16:17:37 CDT