Do you have the option of separating it into two queries by creating a temporary table, something like this (the parenthesis may be off, you'll have to check). I have seen this help quite often: create table worktemp as select * from cdrw.call_detail where 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')); SELECT billing_act_nbr, final_tg_nbr, COUNT(*), SUM (call_duration_value), SUM (rated_cost_amt) FROM worktemp a WHERE orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss') AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss') AND (record_typ='STOP' OR call_duration_value>0) GROUP BY billing_act_nbr, final_tg_nbr; On 10/31/07, A Ebadi <ebadi01@xxxxxxxxx> 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; > > > EXPLAIN PLAN > QUERY_PLAN > > ------------------------------------------------------------------------------------------------------------------------ > SELECT STATEMENT Cost = 32 > 2.1 HASH GROUP BY > 3.1 FILTER > 4.1 PX COORDINATOR > 5.1 PX SEND QC (RANDOM) ":TQ10000" > 6.1 PX BLOCK ITERATOR > 7.1 TABLE ACCESS FULL "CALL_DETAIL" TABLE > 4.2 MINUS > 5.1 SORT UNIQUE NOSORT > 6.1 PARTITION RANGE SINGLE > 7.1 TABLE ACCESS BY LOCAL INDEX ROWID "CALL_DETAIL" TABLE > QUERY_PLAN > > ------------------------------------------------------------------------------------------------------------------------ > 8.1 INDEX RANGE SCAN "CALL_DETAIL_UK" INDEX (UNIQUE) > 5.2 SORT UNIQUE NOSORT > 6.1 PARTITION RANGE SINGLE > 7.1 INDEX RANGE SCAN "CALL_DETAIL_COST_VERO_PK" INDEX (UNIQUE) > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.'