Hi You can also try SELECT 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 NOT EXISTS (SELECT 'X' from cdrw.call_detail_cost_vero d WHERE a.guid=d.guid AND orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss') AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss') ) group by billing_act_nbr, final_tg_nbr regards Hrishy --- Finn Jorgensen <finn.oracledba@xxxxxxxxx> wrote: > The biggest problem for this query is that the IN > results in a FILTER > operation, which is very slow, and I'm guessing the > inner "MINUS" query > returns a lot of rows. > > I'm guessing there's no index on "guid" in > call_detail? > > Try this version (it's hard tuning queries without > access to the database so > a little testing can be done) : > > SELECT /*+ parallel(a,10) use_hash(a b) */ > billing_act_nbr, final_tg_nbr, COUNT(*), > SUM (call_duration_value), > SUM (rated_cost_amt) > FROM cdrw.call_detail a, > ( 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')) > b > WHERE orig_dt >= TO_DATE ('20071016000000', > 'yyyymmddhh24miss') > AND orig_dt < TO_DATE > ('20071016010000', 'yyyymmddhh24miss') > AND (record_typ='STOP' OR > call_duration_value>0) > AND a.guid = b.guid > GROUP BY billing_act_nbr, final_tg_nbr; > > > Finn > > 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 > > > ___________________________________________________________ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/ -- //www.freelists.org/webpage/oracle-l