Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 05 Sep 2008 @ 12:59:38 GMT


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


Subj:   Product join without small table being duplcated on all AMPs
 
From:   DAVID WILLIAMS

Below is a portion of an explain with the database and table names removed. As you can see in step 5 it does a product join of spool 4 and spool 5. Both spool 4 and spool 5 were redistributed. According to documentation, when there is a product join, the smaller table is duplicated on all AMPS. In this case neither table is duplicated on all AMPs. How is this explain possible?

Explanation
--------------------------------------------------
 
  4)We execute the following steps in parallel.  
   
  1) We do an all-AMPs RETRIEVE step from DATABASE1.TABLE1 by way of an all-rows scan with a condition of ("(NOT (DATABASE1.TABLE1.Calendar_Dt IS NULL )) AND ((DATABASE1.TABLE1.Month_ID <= 52096) AND (DATABASE1.TABLE1.Month_ID >= 52085 ))") into Spool 4 (all_amps) (compressed columns allowed), which is redistributed by hash code to all AMPs. The size of Spool 4 is estimated with high confidence to be 1,632 rows. The estimated time for this step is 0.07 seconds.
 
   
  2) We do an all-AMPs RETRIEVE step from DATABASE1.TABLE2 by way of an all-rows scan with a condition of ("DATABASE1.TABLE2.Source = 'XX '") into Spool 5 (all_amps) (compressed columns allowed), which is redistributed by hash code to all AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The result spool file will not be cached in memory. The size of Spool 5 is estimated with low confidence to be 80,946,289 rows. The estimated time for this step is 1 minute and 43 seconds.
 
  5)We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an all-rows scan, which is joined to Spool 5 (Last Use) by way of an all-rows scan. Spool 4 and Spool 5 are joined using a product join, with a join condition of ("ENTRY_DATE = Calendar_Dt"). The result goes into Spool 3 (all_amps) (compressed columns allowed), 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 low confidence to be 80,946,289 rows. The estimated time for this step is 47.63 seconds.  



     
  <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