You didn't specify Oracle version. Will CTE (WITH ...), in this case 2 CTEs work for you? Regards, Igor Neyman On Thu, Jun 9, 2011 at 4:28 PM, Ethan Post <post.ethan@xxxxxxxxx> wrote: > 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 ; > >