Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 01 Jul 2011 @ 10:07:05 GMT


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


Subj:   Spool being used in upi-pi join
 
From:   Anomy Anom

<-- Anonymously Posted: Friday, July 01, 2011 05:33 -->

Hi,

The best way to join two tables is to join on pi s of both the tables as redistribution/duplication is not. However, even though I am joining on pi s of both tables, I am able to see the following step(step num 4) in explain. Ideally step num 5 should start without step 4. Can someone help me in understanding this pl ..

Explanation
--------------------------------------------------
 
  4)We do an all-AMPs RETRIEVE step from db.b by way of an all-rows scan with no residual conditions into Spool 2 (all_amps), which is built locally on the AMPs. The size of Spool 2 is estimated with high confidence to be 1,156,066 rows (28,901,650 bytes). The estimated time for this step is 16.34 seconds.  
  5)We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan, which is joined to db.a by way of a RowHash match scan with no residual conditions. Spool 2 and db.a are joined using a merge join, with a join condition of ("db.a.upi = pi"). The input table db.a will not be cached in memory. The result goes into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 4,096,634 rows (184,348,530 bytes). The estimated time for this step is 36.54 seconds.  


Thank You



     
  <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: 15 Jun 2023