Re: How can I tune this query?????

> aboyes_at_nwu.edu (Andrea J Boyes) writes:
> I have a query:
>
> select id from cat
> where keyval in
> (select keyval from dates
> where year = 1965);
>
> This takes < 5 seconds running against the 300,000 row tables.
>
> When I change the "=" to ">",
> select id from cat
> where keyval in
> (select keyval from dates
> where year > 1965);
>
> the query takes 2 minutes.
>
> Year is type NUMBER and is indexed. Any thoughts on how to
> speed this up?
 >>>>

Howdy. :D

If your keyval's are unique in the dates table, you can get away with this:

select id
from cat, dates
where cat.keyval = dates.keyval
and dates.year > 1965
/

If they aren't, you can add a 'distinct' in there. You may also get some mileage out of this:

select id
from cat
where keyval in
(select keyval from dates
where year > 1965
and dates.keyval = cat.keyval)
/

If keyval is more selective/limiting than year, then the execution should be faster...

Michael Stowe
Constellation Engineering, Inc.
http://www.mcs.com/~stowe Received on Thu Mar 28 1996 - 00:00:00 CET