Re: What hint would you try?

From: Ethan Post <post.ethan_at_gmail.com>
Date: Thu, 9 Jun 2011 15:55:03 -0500
Message-ID: <BANLkTikwUDGOQW9z-JfZGuSicbx0RrhJUQ_at_mail.gmail.com>

I already rewrote another query using traditional Oracle join and it ran great, then was told that would not work because the query is built in PeopleSoft query designer and then PeopleSoft adds the security to the query by rewriting it on the fly when it is run.

On Thu, Jun 9, 2011 at 3:52 PM, Wolfgang Breitling <breitliw_at_centrexcc.com>wrote:

> 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:55:03 CDT