Re: What hint would you try?

From: Igor Neyman <igor.neyman_at_gmail.com>
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-l

Received on Thu Jun 09 2011 - 15:36:08 CDT