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

From: Bob Jones <rpj_at_netusa1.net>
Date: 1996/03/27
Message-ID: <3159EDA2.899_at_netusa1.net>#1/1


Correlated sub-queries work better than IN stt. (for Oracle)

Try something like:

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


Gerard H. Pille wrote:
>
> In article <4jbpgi$ets_001_at_nuts.nwu.edu>, Andrea J Boyes (aboyes_at_nwu.edu)
> says...
> !>
> !>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
>
> Put an index on keyval.
>
> Not satisfied?
>
> select cat.id
> from cat, dates
> where cat.keyval = dates.keyval
> and dates.year > 1965;
>
> You omit the creation of the view on dates (select, order by, distinct)
> Try an explain plan on both, it might prove instructing.
>
> --
> Kind reGards
> \ / |
> X |
> / \ s
> Gerard
  Received on Wed Mar 27 1996 - 00:00:00 CET

Original text of this message