Have you tried using WITH syntax? with svca_ct as (select count(*) svca FROM ((ps_job a INNER JOIN ps_emplmt_srch_qry a1 ON ( a.emplid = a1.emplid AND a.empl_rcd = a1.empl_rcd AND a1.oprid = 'FOO' )) LEFT OUTER JOIN ps_dow_bngap_dtl c ON a.emplid = c.emplid AND c.empl_rcd = 0 AND c.dow_bngap_type = 'SVCAWDDT') ), foo_ct as (select count(*) foo from ((ps_job d INNER JOIN ps_emplmt_srch_qry d1 ON ( d.emplid = d1.emplid AND d.empl_rcd = d1.empl_rcd AND d1.oprid = 'FOO' )) LEFT OUTER JOIN (ps_assignment e INNER JOIN ps_pers_srch_qry e1 ON (e.emplid = e1.emplid AND e1.oprid = 'FOO')) ON d.emplid = e.emplid AND d.asgn_end_dt = e.end_dt) WHERE a.emplid = d.emplid AND a.empl_rcd = d.empl_rcd AND a.effdt = d.effdt AND a.effseq = d.effseq) select svca+foo from svca_ct, foo_ct; Also read here: http://jonathanlewis.wordpress.com/2011/06/08/how-to-hint-1/ You might be able to find the hints that work using Jonathan's technique. David Fitzjarrell ________________________________ From: Ethan Post <post.ethan@xxxxxxxxx> To: oracle-l <oracle-l@xxxxxxxxxxxxx> Sent: Thu, June 9, 2011 1:28:22 PM Subject: What hint would you try? The first two queries run fast enough. However, when they are joined (third query below) on 4 columns which is a solid one to one join it runs really slow. I need to force Oracle to resolve each section first and then do the join. Which hint should I be focusing on, I have tried quite a few and I am not having success. UAT>select count(*) 2 FROM ((ps_job a INNER JOIN ps_emplmt_srch_qry a1 3 ON ( a.emplid = a1.emplid 4 AND a.empl_rcd = a1.empl_rcd 5 AND a1.oprid = 'FOO' 6 )) 7 LEFT OUTER JOIN 8 ps_dow_bngap_dtl c 9 ON a.emplid = c.emplid 10 AND c.empl_rcd = 0 11 AND c.dow_bngap_type = 'SVCAWDDT'); COUNT(*) ---------- 2099422 1 row selected. Elapsed: 00:00:08.34 UAT>select count(*) from ((ps_job d INNER JOIN ps_emplmt_srch_qry d1 2 ON ( d.emplid = d1.emplid 3 AND d.empl_rcd = d1.empl_rcd 4 AND d1.oprid = 'FOO' 5 )) 6 LEFT OUTER JOIN 7 (ps_assignment e INNER JOIN ps_pers_srch_qry e1 8 ON (e.emplid = e1.emplid AND e1.oprid = 'FOO')) 9 ON d.emplid = e.emplid AND d.asgn_end_dt = e.end_dt); COUNT(*) ---------- 2099442 1 row selected. Elapsed: 00:00:06.49 UAT>select count(*) 2 FROM ((ps_job a INNER JOIN ps_emplmt_srch_qry a1 3 ON ( a.emplid = a1.emplid 4 AND a.empl_rcd = a1.empl_rcd 5 AND a1.oprid = 'FOO' 6 )) 7 LEFT OUTER JOIN 8 ps_dow_bngap_dtl c 9 ON a.emplid = c.emplid 10 AND c.empl_rcd = 0 11 AND c.dow_bngap_type = 'SVCAWDDT') 12 , 13 ((ps_job d INNER JOIN ps_emplmt_srch_qry d1 14 ON ( d.emplid = d1.emplid 15 AND d.empl_rcd = d1.empl_rcd 16 AND d1.oprid = 'FOO' 17 )) 18 LEFT OUTER JOIN (ps_assignment e INNER JOIN ps_pers_srch_qry e1 19 20 ON (e.emplid = e1.emplid AND e1.oprid = 'FOO')) 21 ON d.emplid = e.emplid AND d.asgn_end_dt = e.end_dt) 22 WHERE a.emplid = d.emplid 23 AND a.empl_rcd = d.empl_rcd 24 AND a.effdt = d.effdt 25 AND a.effseq = d.effseq 26 ;