Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 27 Jun 2003 @ 19:25:45 GMT


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


Subj:   Re: Exclusion product join explain text
 
From:   Francois GORYN

Hi,

Please find following :

explain select * from table_one A
        where job_code not in ( select  job_code from table_two)
Explanation
--------------------------------------------------
 
  1)First, we lock a distinct DEV_TAMPON."pseudo table" for read on a RowHash to prevent global deadlock for DEV_TAMPON.table_two.  
  2)Next, we lock a distinct DEV_TAMPON."pseudo table" for read on a RowHash to prevent global deadlock for DEV_TAMPON.A.  
  3)We lock DEV_TAMPON.table_two for read, and we lock DEV_TAMPON.A for read.  
  4)We do an all-AMPs RETRIEVE step from DEV_TAMPON.table_two by way of an all-rows scan with no residual conditions into Spool 3 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 3 by the sort key in spool field1 eliminating duplicate rows. The size of Spool 3 is estimated with low confidence to be 2 rows. The estimated time for this step is 0.03 seconds.  
  5)We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan into Spool 2 (all_amps), which is duplicated on all AMPs. The size of Spool 2 is estimated with no confidence to be 4 rows.  
  6)We do an all-AMPs JOIN step from DEV_TAMPON.A by way of an all-rows scan with no residual conditions, which is joined to Spool 2 (Last Use). DEV_TAMPON.A and Spool 2 are joined using an exclusion product join, with a join condition of ("DEV_TAMPON.A.JOB_CODE = JOB_CODE"). The result goes into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with index join confidence to be 2 rows. The estimated time for this step is 0.03 seconds.  
  7)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 0.06 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: 23 Jun 2019