Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 20 Feb 2004 @ 14:45:40 GMT


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


Subj:   Re: Simple question about a left outer join...
 
From:   Richard Gomez

Victor,

Here is the explain plan, and a little more info.

This is the explain that I ran this morning after our processes ran last night.

The row counts have changed, and I reran stats:

tbl1 (Which is wic_np.x in this explain) - 5,273 rows

tbl2 (Which is wic_his.y in this explain) - 5,461,246 rows

(The time has changed significantly from since yesterday.)

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct WIC_NP."pseudo table" for read on a RowHash to prevent global deadlock for WIC_NP.X.  
  2)Next, we lock a distinct WIC_HIS."pseudo table" for read on a RowHash to prevent global deadlock for WIC_HIS.Y.  
  3)We lock WIC_NP.X for read, and we lock WIC_HIS.Y for read.  
  4)We do an all-AMPs RETRIEVE step from WIC_HIS.Y by way of a traversal of index # 4 without accessing the base table with no residual conditions into Spool 2 (all_amps), which is duplicated on 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 2 is estimated with high confidence to be 54,587,480 rows. The estimated time for this step is 34.60 seconds.  
  5)We do an all-AMPs JOIN step from WIC_NP.X by way of an all-rows scan with no residual conditions, which is joined to Spool 2 (Last Use). WIC_NP.X and Spool 2 are left outer joined using a product join, with a join condition of ("(1=0)"). The result goes into Spool 1 (group_amps), which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 1 is estimated with high confidence to be 28,783,978,204 rows. The estimated time for this step is 13 hours and 36 minutes.  
  6)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. The total estimated time is 13 hours and 36 minutes.  


I'm sure I'm missing something obvious, and would appreciate any suggestions.

Rich



     
  <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