Hmm, is a merge join okay enough? I mean if you really want to use a sledge hammer to evaluate the table2 subquery first you can do something like this: SQL> r 1 select something 2 from (select t2.t2rownum, t1.something 3 from table1 t1, 4 (select rownum t2rownum, somethingelse 5 from table2 6 where somethingelse2 between '2' and '4' order by somethingelse) t2 7* where t1.somethingelse = t2.somethingelse) x SOMETHING ---------- A2 A3 A4 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=3 Bytes=63) 1 0 MERGE JOIN (Cost=6 Card=3 Bytes=63) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE1' (TABLE) (Cost= 2 Card=5 Bytes=70) 3 2 INDEX (FULL SCAN) OF 'TABLE1_SOMETHINGELSE' (INDEX) (C ost=1 Card=5) 4 1 SORT (JOIN) (Cost=4 Card=3 Bytes=21) 5 4 VIEW (Cost=3 Card=3 Bytes=21) 6 5 SORT (ORDER BY) (Cost=3 Card=3 Bytes=42) 7 6 COUNT 8 7 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2' (TABLE ) (Cost=2 Card=3 Bytes=42) 9 8 INDEX (RANGE SCAN) OF 'TABLE2_SOMETHINGELSE2' (INDEX) (Cost=1 Card=3) SQL> select * from table1; SOMETHING SOMETHINGE ---------- ---------- A1 B A2 C A3 D A4 E A5 F SQL> select * from table2; SOMETHINGE SOMETHINGE ---------- ---------- B 1 C 2 D 3 E 4 F 5 -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Ken Naim Sent: Friday, November 03, 2006 4:27 PM To: 'Jonathan Lewis'; oracle-l@xxxxxxxxxxxxx Subject: RE: Hint for using a nested loop for an in subquery Thanks, I have used in this query but I am still getting a hash join or a filter instead of nested loops causing the query to run for minutes instead of 285 milliseconds, so what I need is hint that will tell the query to run nested loops against the in subquery? thanks -----Original Message----- From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx] Sent: Friday, November 03, 2006 3:53 PM To: kennaim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx Subject: Re: Hint for using a nested loop for an in subquery If you want to execute the subquery first, the hint you want is UNNEST in the subquery. Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html ----- Original Message ----- From: "Ken Naim" <kennaim@xxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Friday, November 03, 2006 8:10 PM Subject: Hint for using a nested loop for an in subquery >I have used a hint in the past to force a query to use a nested loop join > for a sub query however the name of the hint escapes me. I have googled for > it unsuccessfully. Anyone remember it offhand? > > Query looks like > > Select something > From table1 > Where somethingelse in (select somethingelse from table2 where > somethingelse2 between x and y) > > The ideal plan would evaluate the subquery first then use nested loops > against an index belonging to table1. I have tried using the no_unnest, > various index and use_nl hints unsuccessfully. > > Thanks, > Ken > ---------------------------------------------------------------------------- ---- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.13.24/514 - Release Date: 02/11/2006 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l