(no subject)

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

I'm waiting for "APRIL FOOLS!!"

stowe_at_mcs.net wrote:
>
> > 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 Mon Apr 01 1996 - 00:00:00 CEST