Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sat, 12 Jun 2004 @ 15:30:49 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Performance Improvement on this Query without Collecting Statistics
 
From:   Kambhoji, Naveen Ram Prasanna

Hi Jon,

I have Pasted the Explain plan for your reference ..........

Explanation
--------------------------------------------------
 
  5)We execute the following steps in parallel.  
   
  1) We do an all-AMPs RETRIEVE step from AI_OLAP.dt by way of an all-rows scan with no residual conditions into Spool 4 (all_amps), which is duplicated on all AMPs. Then we do a SORT to order Spool 4 by row hash. The size of Spool 4 is estimated with high confidence to be 17,020 rows. The estimated time for this step is 0.05 seconds.
 
   
  2) We do an all-AMPs RETRIEVE step from AI_OLAP.i by way of an all-rows scan with no residual conditions into Spool 5 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 5 by row hash. The result spool file will not be cached in memory. The size of Spool 5 is estimated with low confidence to be 128,800,340 rows. The estimated time for this step is 14 minutes and 2 seconds.
 
  6)We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a RowHash match scan, which is joined to Spool 5 (Last Use). Spool 4 and Spool 5 are joined using a merge join, with a join condition of ("DateID = DateID"). The result goes into Spool 3 (all_amps), which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 3 is estimated with index join confidence to be 128,800,340 rows. The estimated time for this step is 3 minutes and 55 seconds.  
  7)We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by way of an all-rows scan, and the grouping identifier in field 1. Aggregate Intermediate Results are computed globally, then placed in Spool 6. The aggregate spool file will not be cached in memory. The size of Spool 6 is estimated with low confidence to be 128,800,340 rows. The estimated time for this step is 51 minutes and 8 seconds.  
  8)We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of an all-rows scan into Spool 1 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 1 by row hash. The result spool file will not be cached in memory. The size of Spool 1 is estimated with low confidence to be 128,800,340 rows. The estimated time for this step is 14 minutes and 44 seconds.  
  9)We do an all-AMPs MERGE into AI_OLAP.FA_BCWK_IMP from Spool 1 (Last Use).  


Let me know by this u can help me out.......BTW what is the use of 8th Step???


Thanx!

Prasanna.



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019