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

aboyes_at_nwu.edu (Andrea J Boyes) wrote:
>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?
>
>Thanks in advance for any help!
>
>
>
>Andrea J Boyes
>Northwestern University, Evanston, IL. USA
>aboyes_at_nwu.edu

Try this ...
select id from cat c, dates d
where c.keyval =d.keyval and d.year >1965

If you are running rule base and the above doesn't help try creating indexes for CAT on KEYVAL or for DATES on YEAR

-- 

David M. Taulbee
TRW Vehicle Safety Systems
dtaulbee_at_po.michsb.trw.com