Re: What hint would you try?

Have you tried using WITH syntax?

with svca_ct as (select count(*) svca
   FROM   ((ps_job a INNER JOIN ps_emplmt_srch_qry a1
        ON (    a.emplid = a1.emplid
            AND a.empl_rcd = a1.empl_rcd
            AND a1.oprid = 'FOO'
           ))
        LEFT OUTER JOIN
        ps_dow_bngap_dtl c
        ON a.emplid = c.emplid
      AND c.empl_rcd = 0
      AND c.dow_bngap_type = 'SVCAWDDT')
        ),
    foo_ct as (select count(*) foo
           from ((ps_job d INNER JOIN ps_emplmt_srch_qry d1
        ON (    d.emplid = d1.emplid
            AND d.empl_rcd = d1.empl_rcd
            AND d1.oprid = 'FOO'
           ))
        LEFT OUTER JOIN
       (ps_assignment e INNER JOIN ps_pers_srch_qry e1
        ON (e.emplid = e1.emplid AND e1.oprid = 'FOO'))
        ON d.emplid = e.emplid AND d.asgn_end_dt = e.end_dt)
  WHERE a.emplid = d.emplid
    AND a.empl_rcd = d.empl_rcd
    AND a.effdt = d.effdt
    AND a.effseq = d.effseq)
select svca+foo
from svca_ct, foo_ct;

Also read here:

http://jonathanlewis.wordpress.com/2011/06/08/how-to-hint-1/

You might be able to find the  hints that work using Jonathan's technique.
 
David Fitzjarrell





________________________________
From: Ethan Post <post.ethan_at_gmail.com>
To: oracle-l <oracle-l_at_freelists.org>
Sent: Thu, June 9, 2011 1:28:22 PM
Subject: What hint would you try?

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