Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 02 Oct 2002 @ 17:52:15 GMT


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


Subj:   Re: Left join problem
 
From:   Burton, Bruce

It looks like Teradata uses a hash join with the 'bad' query but a merge join with the 'good' query. Not sure why Teradata decided to use the different methods for the queries...I assume something with the indexing but not sure. Does the 'good' query using sub-selects strip off index info?

Here are the explains from the two queries:

Query that works correctly:
---------------------------

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct mktg_temp."pseudo table" for read on a RowHash to prevent global deadlock for mktg_temp.p065_snet_input.  
  2)Next, we lock mktg_temp.p065_snet_input for read, and we lock CMDMDB.Static for access.  
  3)We execute the following steps in parallel.  
   
  1) We do an all-AMPs RETRIEVE step from mktg_temp.p065_snet_input by way of an all-rows scan with no residual conditions into Spool 2, which is built locally on the AMPs. The size of Spool 2 is estimated with low confidence to be 236,880 rows. The estimated time for this step is 0.22 seconds.
 
   
  2) We do an all-AMPs RETRIEVE step from CMDMDB.Static by way of an all-rows scan with no residual conditions into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 26,605,513 rows. The estimated time for this step is 51.89 seconds.
 
  4)We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan into Spool 4, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 4 by row hash. The size of Spool 4 is estimated with high confidence to be 26,605,513 rows. The estimated time for this step is 1 minute and 27 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 Spool 4 (Last Use). Spool 2 and Spool 4 are left outer joined using a merge join, with a join condition of ("ACCT2 = ACCT"). The result goes into Spool 3, which is built locally on the AMPs. The size of Spool 3 is estimated with no confidence to be 73,019,005 to 6,302,313,919,440 rows. The estimated time for this step is 40.78 seconds to 704 hours and 11 minutes.  
  6)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The contents of Spool 3 are sent back to the user as the result of statement 1. The total estimated time is 2 minutes and 59 seconds.  


Query that *does not* work correctly:
-------------------------------------

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct mktg_temp."pseudo table" for read on a RowHash to prevent global deadlock for mktg_temp.t1.  
  2)Next, we lock mktg_temp.t1 for read, and we lock CMDMDB.Static for access.  
  3)We do an all-AMPs RETRIEVE step from mktg_temp.t1 by way of an all-rows scan with no residual conditions into Spool 2 fanned out into 6 hash join partitions, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated with no confidence to be 236,880 rows.  
  4)We do an all-AMPs RETRIEVE step from Spool 2 by way of an all-rows scan into Spool 3 fanned out into 6 hash join partitions, which is duplicated on all AMPs. The size of Spool 3 is estimated with low confidence to be 66,326,400 rows. The estimated time for this step is 1 minute and 16 seconds.  
  5)We do an all-AMPs RETRIEVE step from CMDMDB.Static by way of an all-rows scan with no residual conditions into Spool 4 fanned out into 6 hash join partitions, which is built locally on the AMPs. The size of Spool 4 is estimated with high confidence to be 26,605,513 rows. The estimated time for this step is 1 minute and 40 seconds.  
  6)We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an all-rows scan, which is joined to Spool 4 (Last Use). Spool 3 and Spool 4 are joined using a hash join of 6 partitions, with a join condition of ("acct = acct"). The result goes into Spool 5, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 5 by row hash. The size of Spool 5 is estimated with low confidence to be 316,810 rows. The estimated time for this step is 1 minute and 39 seconds.  
  7)We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan., which is joined to Spool 5 (Last Use). Spool 2 and Spool 5 are left outer joined using a merge join of 6 partitions, with a join condition of ("Field_1 = Field_1"). The result goes into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 316,810 rows. The estimated time for this step is 0.16 seconds.  
  8)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 4 minutes and 35 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