Re: What hint would you try?

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 9 Jun 2011 14:52:03 -0600
Message-Id: <1E170368-997F-4B16-9C5C-48E17D7512BF_at_centrexcc.com>

For hints NO_UNNEST and NO_MERGE come to mind.

I would also try what you get when you replace the ANSI join syntax with the "traditional" Oracle join syntax. The optimizer doesn't speak ANSI very well.

On 2011-06-09, at 2:28 PM, Ethan Post 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:52:03 CDT