Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 19 Feb 2002 @ 16:44:13 GMT


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


Subj:   Update SQL Spool Problem
 
From:   Jay Quinn

Hi, I am trying to Update an existing temp table from a regular much larger table that has accumulated inserts and updates since the temp table was last created and inserted. The query keeps Spooling out, I've tried a variety of things with smaller sets of data but without success, the Explain doesn't really tell me much, I have attached it too. What is the best way to tune a SQL like this? I'm sure it's gotta be the use of a Derived table, but I can't figure it out. Thanks, Jay

UPDATE special_tables.pc6234_tax
SET
TAX_SERV_GEO = VSLC041_TAX.TAX_SERV_GEO,
TAX_ADR_GEO = VSLC041_TAX.TAX_ADR_GEO,
TAX_ACTL_SIZE = VSLC041_TAX.TAX_ACTL_SIZE,
TAX_AUTH_1 = VSLC041_TAX.TAX_AUTH_1,
TAX_TYPE_1 = VSLC041_TAX.TAX_TYPE_1,
TAX_AMT_1 = VSLC041_TAX.TAX_AMT_1,
TAX_AUTH_2 = VSLC041_TAX.TAX_AUTH_2,
TAX_TYPE_2 = VSLC041_TAX.TAX_TYPE_2,
TAX_AMT_2 = VSLC041_TAX.TAX_AMT_2,
TAX_AUTH_3 = VSLC041_TAX.TAX_AUTH_3,
TAX_TYPE_3 = VSLC041_TAX.TAX_TYPE_3,
TAX_AMT_3 = VSLC041_TAX.TAX_AMT_3,
TAX_AUTH_4 = VSLC041_TAX.TAX_AUTH_4,
TAX_TYPE_4 = VSLC041_TAX.TAX_TYPE_4,
TAX_AMT_4 = VSLC041_TAX.TAX_AMT_4,
TAX_AUTH_5 = VSLC041_TAX.TAX_AUTH_5,
TAX_TYPE_5 = VSLC041_TAX.TAX_TYPE_5,
TAX_AMT_5 = VSLC041_TAX.TAX_AMT_5,
TAX_AUTH_6 = VSLC041_TAX.TAX_AUTH_6,
TAX_TYPE_6 = VSLC041_TAX.TAX_TYPE_6,
TAX_AMT_6 = VSLC041_TAX.TAX_AMT_6,
TAX_AUTH_7 = VSLC041_TAX.TAX_AUTH_7,
TAX_TYPE_7 = VSLC041_TAX.TAX_TYPE_7,
TAX_AMT_7 = VSLC041_TAX.TAX_AMT_7,
TAX_AUTH_8 = VSLC041_TAX.TAX_AUTH_8,
TAX_TYPE_8 = VSLC041_TAX.TAX_TYPE_8,
TAX_AMT_8 = VSLC041_TAX.TAX_AMT_8,
TAX_AUTH_9 = VSLC041_TAX.TAX_AUTH_9,
TAX_TYPE_9 = VSLC041_TAX.TAX_TYPE_9,
TAX_AMT_9 = VSLC041_TAX.TAX_AMT_9
WHERE
        (PC6234_TAX.ban = VSLC041_TAX.ban
        and PC6234_TAX.ent_seq_no = VSLC041_TAX.orig_ent_seq_no
        and PC6234_TAX.actv_seq_no = VSLC041_TAX.orig_actv_seq_no
        and PC6234_TAX.actv_code = VSLC041_TAX.actv_code)

The Explain:

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct special_tables."pseudo table" for write on a RowHash to prevent global deadlock for special_tables.pc6234_tax.  
  2)Next, we lock CUSTOMER.TSLC041_TAX for access, and we lock special_tables.pc6234_tax for write.  
  3)We execute the following steps in parallel.  
   
  1) We do an all-AMPs RETRIEVE step from special_tables.pc6234_tax by way of an all-rows scan with no residual conditions into Spool 2, which is redistributed by hash code to all AMPs. The size of Spool 2 is estimated with high confidence to be 8,250,812 rows. The estimated time for this step is 7.79 seconds.
 
   
  2) We do an all-AMPs RETRIEVE step from CUSTOMER.TSLC041_TAX by way of an all-rows scan with no residual conditions into Spool 3, which is redistributed by hash code to all AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 3 is estimated with high confidence to be 65,448,708 rows. The estimated time for this step is 1 minute and 53 seconds.
 
  4)We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to Spool 3 (Last Use). Spool 2 and Spool 3 are joined using a single partition hash join, with a join condition of ("((BAN )= (BAN (CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT '---------9.')(CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, NAMED BAN )(FLOAT, FORMAT '-9.99999999999999E- 999')LATIN)) AND ((ENT_SEQ_NO = ORIG_ENT_SEQ_NO) AND ((ACTV_SEQ_NO = ORIG_ACTV_SEQ_NO) AND (ACTV_CODE = ACTV_CODE )))"). The result goes into Spool 1, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1. The size of Spool 1 is estimated with low confidence to be 237,745,954 rows. The estimated time for this step is 4 minutes and 17 seconds.  
  5)We do a MERGE DELETE to special_tables.pc6234_tax from Spool 1 (Last Use) via the row id. New updated rows are built and the result goes into Spool 4, which is built locally on the AMPs. Then we do a SORT to order Spool 4 by row hash.  
  6)We do a MERGE into special_tables.pc6234_tax from Spool 4 (Last Use).  
  7)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->No rows are returned to the user as the result of statement 1.  


Jay Quinn
Enterprise Data Warehouse



     
  <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