Re: What hint would you try?
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 9 Jun 2011 13:42:02 -0700 (PDT)
Message-ID: <109737.74609.qm_at_web65410.mail.ac4.yahoo.com>
Date: Thu, 9 Jun 2011 13:42:02 -0700 (PDT)
Message-ID: <109737.74609.qm_at_web65410.mail.ac4.yahoo.com>
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-lReceived on Thu Jun 09 2011 - 15:42:02 CDT