Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 17 Dec 2002 @ 16:21:07 GMT


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


Subj:   Unnecessary spool step
 
From:   Kumaran Anantaraman

Hi,

In the following explain plan, I see that spool 2 is created in step 1, which is then all-rows-scan read (last use), and put in spool 1 in step 3.1.

So, what is the purpose of step 3.1? Spool 1 and spool 2 are same, and step 2 could have directly created spool 1.

The explain plan below is exactly what I got with table names changed.

Explanation
--------------------------------------------------
 
  1)First, we lock xxx_DB.yyy_TBL for access.  
  2)Next, we do a SUM step to aggregate from xxx_DB.yyy_TBL by way of an all-rows scan with a condition of ("(xxx_DB.yyy_TBL.date_col >= DATE '2002-12-02') AND(xxx_DB.yyy_TBL.date_col <= DATE '2002-12-04')"), and the grouping identifier in field 1030. Aggregate Intermediate Results are computed globally, then placed in Spool 2. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 2 is estimated with low confidence to be 101 rows.  
  3)We execute the following steps in parallel.  
   
  1) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of an all-rows scan into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 101 rows. The estimated time for this step is 0.17 seconds.
 
   
  2) We do a SUM step to aggregate from xxx_DB.yyy_TBL by way of an all-rows scan with a condition of ("(xxx_DB.yyy_TBL.date_col >= DATE '2002-12-02') AND (xxx_DB.yyy_TBL.date_col <= DATE '2002-12-04')"), and the grouping identifier in field 1. Aggregate Intermediate Results are computed globally, then placed in Spool 4. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 4 is estimated with high confidence to be 793,963 rows.
 
  4)We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of an all-rows scan with a condition of ("Field_4 > 0") into Spool 1, which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1. The size of Spool 1 is estimated with high confidence to be 264,755 to 793,963 rows. The estimated time for this step is 0.66 to 1.56 seconds.  
  5)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The contents of Spool 1 are sent back to the user as the result of statement 1.  



     
  <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: 27 Dec 2016