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

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