Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 26 Aug 2014 @ 20:33:39 GMT


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


Subj:   Left Join leading to product Join
 
From:   Bakthavachalam, Roopalini

Hello Forum -

I am trying to understand the EXPLAIN of the below query and I could understand everything except the product join that is happening at the end. Could someone help me understand why a product join is happening in the end.

This is just a part of the query. I have few more LEFT JOINS and the query is running out of spool (due to the product Joins happening)

     SELECT

     COUNT(*)

     FROM
     RPT_SPEC_WEIGHT            A11
     LEFT OUTER JOIN
     PLM_SPEC_ALLERGEN_VW        A12
     ON         (A11.PRODUCT_GN_NBR = A12.PRODUCT_GN_NBR) WHERE A11.PRODUCT_GN_NBR = '2874.80469'


     Explain SELECT

     COUNT(*)

     FROM RPT_SPEC_WEIGHT            A11
                     LEFT OUTER JOIN PLM_SPEC_ALLERGEN_VW        A12
                          ON (A11.PRODUCT_GN_NBR = A12.PRODUCT_GN_NBR)
                          WHERE A11.PRODUCT_GN_NBR = '2874.80469'
Explanation
--------------------------------------------------
 
  1)First, we lock BAKTHRO.PLM_PROD_SPEC in view PLM_SPEC_ALLERGEN_VW for access, and we lock BAKTHRO.A in view PLM_SPEC_ALLERGEN_VW for access.  
  2)Next, we do a single-AMP SUM step to aggregate from BAKTHRO.P in view RPT_SPEC_WEIGHT by way of the primary index "BAKTHRO.P in view RPT_SPEC_WEIGHT.PRODUCT_GN_NBR = '2874.80469 '" with no residual conditions , grouping by field1 (BAKTHRO.P.PRODUCT_GN_NBR) locking row for access. Aggregate Intermediate Results are computed locally, then placed in Spool 5. The size of Spool 5 is estimated with high confidence to be 1 row (43 bytes). The estimated time for this step is 0.00 seconds.  
  3)We execute the following steps in parallel.  
   
  1) We do a single-AMP RETRIEVE step from Spool 5 (Last Use) by way of the hash value of "BAKTHRO.P in view RPT_SPEC_WEIGHT.PRODUCT_GN_NBR = '2874.80469 '" into Spool 3 (used to materialize view, derived table or table function A11) (one-amp), which is built locally on that AMP. The size of Spool 3 is estimated with high confidence to be 1 row (36 bytes). The estimated time for this step is 0.01 seconds.
 
   
  2) We do an all-AMPs SUM step to aggregate from BAKTHRO.PLM_PROD_SPEC in view PLM_SPEC_ALLERGEN_VW by way of an all-rows scan with a condition of ("NOT (BAKTHRO.PLM_PROD_SPEC in view PLM_SPEC_ALLERGEN_VW.PRODUCT_GN_NBR IS NULL)"), grouping by field1 ( BAKTHRO.PLM_PROD_SPEC.PRODUCT_GN_NBR). Aggregate Intermediate Results are computed globally, then placed in Spool 8. The size of Spool 8 is estimated with high confidence to be 46,716 rows (2,055,504 bytes). The estimated time for this step is 0.14 seconds.
 
  4)We do an all-AMPs RETRIEVE step from Spool 8 (Last Use) by way of an all-rows scan into Spool 1 (used to materialize view, derived table or table function MAJOR) (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 46,716 rows (1,728,492 bytes). The estimated time for this step is 0.03 seconds.  
  5)We execute the following steps in parallel.  
   
  1) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan into Spool 12 (all_amps), which is redistributed by the hash code of (BAKTHRO.PLM_PROD_SPEC.PRODUCT_GN_NBR, BAKTHRO.PLM_PROD_SPEC.MAJOR_VER) to all AMPs. The size of Spool 12 is estimated with high confidence to be 46,716 rows (1,354,764 bytes). The estimated time for this step is 0.05 seconds.
 
   
  2) We do an all-AMPs RETRIEVE step from BAKTHRO.MINOR in view PLM_SPEC_ALLERGEN_VW by way of an all-rows scan with no residual conditions locking for access into Spool 13 (all_amps), which is redistributed by the hash code of (BAKTHRO.MINOR.PRODUCT_GN_NBR, BAKTHRO.MINOR.MAJOR_VER) to all AMPs. The size of Spool 13 is estimated with high confidence to be 98,112 rows (3,237,696 bytes). The estimated time for this step is 0.11 seconds.
 
  6)We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of an all-rows scan, which is joined to Spool 13 (Last Use) by way of an all- rows scan. Spool 12 and Spool 13 are joined using a single partition hash join, with a join condition of ("(PRODUCT_GN_NBR = PRODUCT_GN_NBR) AND (MAJOR_VER = MAJOR_VER)"). The result goes into Spool 11 (all_amps), which is built locally on the AMPs. The size of Spool 11 is estimated with low confidence to be 64,282 rows (2,249,870 bytes). The estimated time for this step is 0.04 seconds.  
  7)We do an all-AMPs SUM step to aggregate from Spool 11 (Last Use) by way of an all-rows scan , grouping by field1 (BAKTHRO.MINOR.PRODUCT_GN_NBR ,BAKTHRO.PLM_PROD_SPEC.MAJOR_VER). Aggregate Intermediate Results are computed globally, then placed in Spool 14. The size of Spool 14 is estimated with low confidence to be 46,716 rows (2,662,812 bytes). The estimated time for this step is 0.08 seconds.  
  8)We execute the following steps in parallel.  
   
  1) We do an all-AMPs RETRIEVE step from Spool 14 (Last Use) by way of an all-rows scan into Spool 2 (used to materialize view, derived table or table function CUR) (all_amps), which is built locally on the AMPs. The size of Spool 2 is estimated with low confidence to be 46,716 rows (1,915,356 bytes). The estimated time for this step is 0.03 seconds.
 
   
  2) We do an all-AMPs RETRIEVE step from BAKTHRO.SPEC in view PLM_SPEC_ALLERGEN_VW by way of a traversal of index # 4 extracting row ids only with a residual condition of ("(BAKTHRO.SPEC in view PLM_SPEC_ALLERGEN_VW.PRODUCT_GN_NBR = '2874.80469 ') AND ((NOT (BAKTHRO.SPEC in view PLM_SPEC_ALLERGEN_VW.SPEC_ID IS NULL )) AND (NOT (BAKTHRO.SPEC in view PLM_SPEC_ALLERGEN_VW.PRODUCT_GN_NBR IS NULL )))") locking for access into Spool 19 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 19 by row id eliminating duplicate rows. The size of Spool 19 is estimated with high confidence to be 0 row. The estimated time for this step is 0.03 seconds.
 
  9)We do an all-AMPs JOIN step from BAKTHRO.SPEC in view PLM_SPEC_ALLERGEN_VW by way of row ids from Spool 19 (Last Use) with a residual condition of ("BAKTHRO.SPEC in view PLM_SPEC_ALLERGEN_VW.SNAPSHOT_IND = 'NO '"), which is joined to BAKTHRO.A in view PLM_SPEC_ALLERGEN_VW by way of a RowHash match scan with no residual conditions. BAKTHRO.SPEC and BAKTHRO.A are joined using a merge join, with a join condition of ("BAKTHRO.A.spec_id = BAKTHRO.SPEC.SPEC_ID"). The result goes into Spool 20 (all_amps), which is duplicated on all AMPs. The size of Spool 20 is estimated with low confidence to be 1,584 rows (52,272 bytes). The estimated time for this step is 0.06 seconds.  
  10)We do an all-AMPs JOIN step from Spool 20 (Last Use) by way of an all-rows scan, which is joined to Spool 2 (Last Use) by way of an all- rows scan. Spool 20 and Spool 2 are joined using a single partition hash_ join, with a join condition of ("(PRODUCT_GN_NBR = PRODUCT_GN_NBR) AND ((MAJOR_VER = MAJOR_VER) AND (MINOR_VER = MINOR_VER ))"). The result goes into Spool 21 (all_amps), which is duplicated on all AMPs. The size of Spool 21 is estimated with low confidence to be 1,584 rows (44,352 bytes). The estimated time for this step is 0.03 seconds.  
  11)We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of the hash value of "BAKTHRO.P in view RPT_SPEC_WEIGHT.PRODUCT_GN_NBR = '2874.80469 '" with an additional condition of ("A11.PRODUCT_GN_NBR = '2874.80469 '"), which is joined to Spool 21 (Last Use) by way of an all- rows scan. Spool 3 and Spool 21 are left outer joined using a product join, with condition(s) used for non-matching on left table ("PRODUCT_GN_NBR = '2874.80469 '"), with a join condition of ("PRODUCT_GN_NBR = PRODUCT_GN_NBR"). The result goes into Spool 18 (all_amps), which is built locally on the AMPs. The size of Spool 18 is estimated with low confidence to be 33 rows (495 bytes). The estimated time for this step is 0.02 seconds.  
  12)We do an all-AMPs SUM step to aggregate from Spool 18 (Last Use) by way of a cylinder index scan. Aggregate Intermediate Results are computed globally, then placed in Spool 22. The size of Spool 22 is estimated with high confidence to be 1 row (23 bytes). The estimated time for this step is 0.03 seconds.  
  13)We do an all-AMPs RETRIEVE step from Spool 22 (Last Use) by way of an all-rows scan into Spool 16 (group_amps), which is built locally on the AMPs. The size of Spool 16 is estimated with high confidence to be 1 row (25 bytes). The estimated time for this step is 0.01 seconds.  
  14)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The contents of Spool 16 are sent back to the user as the result of statement 1. The total estimated time is 0.57 seconds.  


Thanks

Roopalini



     
  <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