 |
 |
Archives of the TeradataForum
Message Posted: Tue, 26 Jun 2012 @ 13:59:49 GMT
| 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.
| |
| |
