Ed Dressel wrote:

>> If I have problems with this approach then I would try the combination
>> of the pairs.
>>
>
> I tried the following, and was quite surprised by the results. (Note
> that I disconnect and shut the service down between trials).
>
> select count(*) from ClientInfo
> where (Upper_LastName = 'SOME') and (Upper_FirstName = 'NAME')
>
> Plan
> PLAN (CLIENTINFO INDEX (CLIENTINFO_IDX5))
>
> Adapted Plan
> PLAN (CLIENTINFO INDEX (CLIENTINFO_IDX5))
>
> ------ Performance info ------
> Prepare time = 47ms
> Execute time = 62ms
> Avg fetch time = 62.00 ms
> Current memory = 721,344
> Max memory = 850,832
> Memory buffers = 2,048
> Reads from disk to cache = 35
> Writes from cache to disk = 0
> Fetches from cache = 61
>
> Then without the indexes:
>
> select count(*) from ClientInfo
> where Upper(Last_Name) = 'SOME' and Upper(First_Name) = 'NAME'
>
> Plan
> PLAN (CLIENTINFO NATURAL)
>
> Adapted Plan
> PLAN (CLIENTINFO NATURAL)
>
> ------ Performance info ------
> Prepare time = 31ms
> Execute time = 125ms
> Avg fetch time = 125.00 ms
> Current memory = 712,216
> Max memory = 850,832
> Memory buffers = 2,048
> Reads from disk to cache = 1,592
> Writes from cache to disk = 0
> Fetches from cache = 17,016
>
>
> I can not imagine why the two are even close--but maybe 2x speed
> improvement is all I will get with adding an index.
>
> Is this typical?
>
>

How many rows on the table ?

Maybe the OS cache is playing around, try rebooting the server and do
the select without indices.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br