Re: What hint would you try?

  • From: Igor Neyman <igor.neyman@xxxxxxxxx>
  • To: post.ethan@xxxxxxxxx
  • Date: Thu, 9 Jun 2011 16:36:08 -0400

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  ;
>
>

Other related posts: