Re: Query Tuning Help

Let me have a try at it ...
SELECT a.billing_act_nbr, a.final_tg_nbr, COUNT(*), SUM (a.call_duration_value), 
        SUM (a.rated_cost_amt)
   FROM cdrw.call_detail a
         left outer join (select guid
                                  from drw.call_detail_cost_vero
where orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss') and orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss')) b 
                     on a.guid = b.guid
WHERE a.orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss') AND a.orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss') 
                       AND (a.record_typ='STOP' OR a.call_duration_value>0)
   and b.guid is null
 GROUP BY a.billing_act_nbr, a.final_tg_nbr;

I always suppress hints until proved necessary ...

Hope it works better ...

SF


A Ebadi wrote:
We've been trying to tune this query below, but to no avail. The table call_detail below is a very large hourly partitioned table with each partition being about 2-4GB! The other table (call_detail_cost_vero) is relatively small. The two inner selects with minus runs fine alone in about 6-8 minutes, but the entire query doesn't finish even after running it for many hours! We've tried running it in parallel/no parallel and hasn't helped. Any recommendations would be appreciated. The explain plan is at the bottom of this e-mail also. Thanks, 
Abdul
Environment: Sun Solaris, Oracle 10.2.0.3.0 on RAC (4 node) SELECT /*+ parallel(a,10) */ billing_act_nbr, final_tg_nbr, COUNT(*), SUM (call_duration_value), 
         SUM (rated_cost_amt)
    FROM cdrw.call_detail a
WHERE orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss') AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss') 
                        AND (record_typ='STOP' OR call_duration_value>0)
     AND guid IN (
            SELECT
                   guid
              FROM cdrw.call_detail c
WHERE orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss') AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss') 
                        AND (record_typ='STOP' OR call_duration_value>0)
            MINUS
            SELECT
                   guid
              FROM cdrw.call_detail_cost_vero d
WHERE orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss') AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss')) 
GROUP BY billing_act_nbr, final_tg_nbr;



--
http://www.freelists.org/webpage/oracle-l

Other related posts: