What hint would you try?

From: Ethan Post <post.ethan_at_gmail.com>
Date: Thu, 9 Jun 2011 15:28:22 -0500
Message-ID: <BANLkTimUQv0uzag2cDWPYhw4pFXUm3BBvA_at_mail.gmail.com>

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:28:22 CDT