Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 26 Jun 2012 @ 13:59:49 GMT


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


Subj:   Re: How to run Update statement faster
 
From:   Anomy Anom

<-- Anonymously Posted: Tuesday, June 26, 2012 07:54 -->

Is T1.c1 a PI or Partition column? T1.C1 is part of the composite PI

Is the T1 SET or MULTISET? It's a SET table

Is the PI of T1 very non-unique? -> YES it is very non-unique

Here is the Explain

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct Devl_Trade."pseudo table" for write on a RowHash to prevent global deadlock for Devl_Trade.SUMMARY_STOCK_STORE_DEPT.  
  2)Next, we lock a distinct Devl_Trade."pseudo table" for read on a RowHash to prevent global deadlock for Devl_Trade.SSA_STORE.  
  3)We lock Devl_Trade.SUMMARY_STOCK_STORE_DEPT for write, and we lock Devl_Trade.SSA_STORE for read.  
  4)We do an all-AMPs RETRIEVE step from Devl_Trade.SSA_STORE by way of an all-rows scan with no residual conditions into Spool 2 (all_amps), which is duplicated on all AMPs. The size of Spool 2 is estimated with low confidence to be 137,500 rows (3,025,000 bytes). The estimated time for this step is 0.02 seconds.  
  5)We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to Devl_Trade.SUMMARY_STOCK_STORE_DEPT by way of an all-rows scan with no residual conditions. Spool 2 and Devl_Trade.SUMMARY_STOCK_STORE_DEPT are joined using a single partition hash_ join, with a join condition of ("Devl_Trade.SUMMARY_STOCK_STORE_DEPT.STORE_CD_OLD = STORE_CD_OLD"). The result goes into Spool 1 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1 (Devl_Trade.SUMMARY_STOCK_STORE_DEPT.ROWID). The size of Spool 1 is estimated with low confidence to be 2,750 rows (60,500 bytes). The estimated time for this step is 0.02 seconds.  
  6)We do an all-AMPs MERGE DELETE to Devl_Trade.SUMMARY_STOCK_STORE_DEPT from Spool 1 (Last Use) via the row id. New updated rows are built and the result goes into Spool 3 (all_amps), which is redistributed by the hash code of (Devl_Trade.SUMMARY_STOCK_STORE_DEPT.Field_1025, Devl_Trade.SUMMARY_STOCK_STORE_DEPT.Field_1026, Devl_Trade.SSA_STORE.STORE_CD) to all AMPs. Then we do a SORT to order Spool 3 by row hash. The size is estimated with low confidence to be 2,750 rows. The estimated time for this step is 5.49 seconds.  
  7)We do an all-AMPs MERGE into Devl_Trade.SUMMARY_STOCK_STORE_DEPT from Spool 3 (Last Use). The size is estimated with low confidence to be 2,750 rows. The estimated time for this step is 1 second.  
  8)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. The total estimated time is 6.52 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: 15 Jun 2023