 |
 |
Archives of the TeradataForum
Message Posted: Tue, 19 Feb 2002 @ 16:44:13 GMT
| 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
| |
