# Re: What hint would you try?

Date: Thu, 9 Jun 2011 16:36:08 -0400

Message-ID: <BANLkTiku8W_bTCpiKEWcz-UM7Jd2VYyZHw_at_mail.gmail.com>

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_at_gmail.com> 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 ;*

*>*

*>*

-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 09 2011 - 15:36:08 CDT