 |
 |
Archives of the TeradataForum
Message Posted: Wed, 18 Jun 2014 @ 22:13:27 GMT
| Subj: | | Re: SQL with product join |
| |
| From: | | JAMES PARK |
*** Changed DB and table and view names out of concern***
EXPLAIN SELECT
VWDB.VIEW1.PRIM_ID_IND,
VWDB.VIEW1.STAT_CD,
VWDB.VIEW2.PRD_STATUS_CD,
VWDB.VIEW2.DW_VER_END_DT,
VWDB.VIEW2.ACCT_TYPE_CD,
VWDB.VIEW2.TAX_PRD_END_DT,
VWDB.VIEW3.SMRT_INTERNAL_ID,
VWDB.VIEW1.ID_TYPE_CD,
VWDB.VIEW3.RELSHP_END_DT
FROM
VWDB.VIEW1,
VWDB.VIEW2 RIGHT JOIN VWDB.TAX_ACCOUNT_LIST ON
VWDB.TAX_ACCOUNT_LIST.SMRT_INTERNAL_ID=VWDB.VIEW2.SMRT_INTERNAL_ID AND
VWDB.TAX_ACCOUNT_LIST.ACCT_TYPE_CD=VWDB.VIEW2.ACCT_TYPE_CD AND
VWDB.TAX_ACCOUNT_LIST.CR_NUM=VWDB.VIEW2.CR_NUM
LEFT JOIN VWDB.TAX_ACCT_FINANCIAL_TRANSACTION ON
VWDB.TAX_ACCOUNT_LIST.SMRT_INTERNAL_ID=VWDB.TAX_ACCT_FINANCIAL_TRANSACTION.SMRT_INTERNAL_ID AND
VWDB.TAX_ACCOUNT_LIST.ACCT_TYPE_CD=VWDB.TAX_ACCT_FINANCIAL_TRANSACTION.ACCT_TYPE_CD AND
VWDB.TAX_ACCOUNT_LIST.CR_NUM=VWDB.TAX_ACCT_FINANCIAL_TRANSACTION.CR_NUM,
VWDB.TAX_ACCT_FINANCIAL_TRANS_LIST,
VWDB.VIEW1_List LEFT JOIN VWDB.VIEW3 ON
VWDB.VIEW1_List.SMRT_INTERNAL_ID=VWDB.VIEW3.SMRT_INTERNAL_ID
WHERE
( VWDB.VIEW1_List.SMRT_INTERNAL_ID=VWDB.VIEW1.SMRT_INTERNAL_ID )
AND (
VWDB.TAX_ACCT_FINANCIAL_TRANS_LIST.SMRT_INTERNAL_ID=VWDB.VIEW1.SMRT_INTERNAL_ID )
AND
(
VWDB.VIEW3.RELSHP_END_DT IN ( {d '2010-12-31'} )
AND
VWDB.VIEW1.ID_TYPE_CD IN ( 2 )
AND
(
VWDB.VIEW1.PRIM_ID_IND = 'n'
OR
VWDB.VIEW1.STAT_CD <> 1
OR
VWDB.VIEW2.PRD_STATUS_CD <> 6
OR
VWDB.VIEW2.DW_VER_END_DT <> {d '9999-12-31'}
OR
VWDB.VIEW2.ACCT_TYPE_CD <> 100
OR
VWDB.VIEW2.TAX_PRD_END_DT <> {d '9999-12-31'}
)
)
EXPLAIN
SELECT
VWDB.VIEW1.PRIM_ID_IND,
VWDB.VIEW1.STAT_CD,
VWDB.VIEW2.PRD_STATUS_CD,
VWDB.VIEW2.DW_VER_END_DT,
VWDB.VIEW2.ACCT_TYPE_CD,
VWDB.VIEW2.TAX_PRD_END_DT,
VWDB.VIEW3.SMRT_INTERNAL_ID,
VWDB.VIEW1.ID_TYPE_CD,
VWDB.VIEW3.RELSHP_END_DT
FROM
VWDB.VIEW1,
VWDB.VIEW2 RIGHT JOIN VWDB.TAX_ACCOUNT_LIST ON
VWDB.TAX_ACCOUNT_LIST.SMRT_INTERNAL_ID=VWDB.VIEW2.SMRT_INTERNAL_ID AND
VWDB.TAX_ACCOUNT_LIST.ACCT_TYPE_CD=VWDB.VIEW2.ACCT_TYPE_CD AND
VWDB.TAX_ACCOUNT_LIST.CR_NUM=VWDB.VIEW2.CR_NUM
LEFT JOIN VWDB.TAX_ACCT_FINANCIAL_TRANSACTION ON
VWDB.TAX_ACCOUNT_LIST.SMRT_INTERNAL_ID=VWDB.TAX_ACCT_FINANCIAL_TRANSACTION.SMRT_INTERNAL_ID AND
VWDB.TAX_ACCOUNT_LIST.ACCT_TYPE_CD=VWDB.TAX_ACCT_FINANCIAL_TRANSACTION.ACCT_TYPE_CD AND
VWDB.TAX_ACCOUNT_LIST.CR_NUM=VWDB.TAX_ACCT_FINANCIAL_TRANSACTION.CR_NUM,
VWDB.TAX_ACCT_FINANCIAL_TRANS_LIST,
VWDB.VIEW1_List LEFT JOIN VWDB.VIEW3 ON
VWDB.VIEW1_List.SMRT_INTERNAL_ID=VWDB.VIEW3.SMRT_INTERNAL_ID
WHERE
( VWDB.VIEW1_List.SMRT_INTERNAL_ID=VWDB.VIEW1.SMRT_INTERNAL_ID )
AND (
VWDB.TAX_ACCT_FINANCIAL_TRANS_LIST.SMRT_INTERNAL_ID=VWDB.VIEW1.SMRT_INTERNAL_ID )
AND
(
VWDB.VIEW3.RELSHP_END_DT IN ( {d '2010-12-31'} )
AND
VWDB.VIEW1.ID_TYPE_CD IN ( 2 )
AND
(
VWDB.VIEW1.PRIM_ID_IND = 'n'
OR
VWDB.VIEW1.STAT_CD <> 1
OR
VWDB.VIEW2.PRD_STATUS_CD <> 6
OR
VWDB.VIEW2.DW_VER_END_DT <> {d '9999-12-31'}
OR
VWDB.VIEW2.ACCT_TYPE_CD <> 100
OR
VWDB.VIEW2.TAX_PRD_END_DT <> {d '9999-12-31'}
)
)
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock TBLDB.VIEW1_List in view VWDB.VIEW1_List for access, we lock TBLDB.TAX_ACCT_FINANCIAL_TRANS_LIST in view
VWDB.TAX_ACCT_FINANCIAL_TRANS_LIST for access, we lock TBLDB.TAX_ACCOUNT_LIST in view VWDB.TAX_ACCOUNT_LIST for access, we lock TBLDB.VIEW2 in
view VWDB.VIEW2 for access, we lock TBLDB.TAX_ACCT_FINANCIAL_TRANSACTION in view VWDB.TAX_ACCT_FINANCIAL_TRANSACTION for access, we lock
TBLDB.VIEW3 in view VWDB.VIEW3 for access, and we lock TBLDB.VIEW1 in view VWDB.VIEW1 for access.
| |
| | 2) | Next, we do an all-AMPs RETRIEVE step from TBLDB.VIEW3 in view VWDB.VIEW3 by way of an all-rows scan with a condition of ("TBLDB.VIEW3 in
view VWDB.VIEW3.RELSHP_END_DT = DATE '2010-12-31'") into Spool 2 (all_amps) (compressed columns allowed), which is built locally on the AMPs. The
size of Spool 2 is estimated with no confidence to be 741,504 rows (18,537,600 bytes). The estimated time for this step is 1.31 seconds.
| |
| | 3) | We do an all-AMPs JOIN step from TBLDB.VIEW1 in view VWDB.VIEW1 by way of a RowHash match scan with a condition of ("TBLDB.VIEW1 in view
VWDB.VIEW1.ID_TYPE_CD = 2"), which is joined to Spool 2 (Last Use) by way of a RowHash match scan. TBLDB.VIEW1 and Spool 2 are joined using a
merge join, with a join condition of ("SMRT_INTERNAL_ID = TBLDB.VIEW1.SMRT_INTERNAL_ID"). The result goes into Spool 3 (all_amps) (compressed
columns allowed), which is built locally on the AMPs. The size of Spool 3 is estimated with no confidence to be 256,566 rows (9,749,508 bytes).
The estimated time for this step is 1.93 seconds.
| |
| | 4) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of a RowHash match scan, which is joined to TBLDB.VIEW1_List in view
VWDB.VIEW1_List by way of a RowHash match scan. Spool 3 and TBLDB.VIEW1_List are joined using a merge join, with a join condition of
("(TBLDB.VIEW1_List.SMRT_INTERNAL_ID = SMRT_INTERNAL_ID) AND (TBLDB.VIEW1_List.SMRT_INTERNAL_ID = SMRT_INTERNAL_ID)"). The result goes into Spool
4 (all_amps) (compressed columns allowed), which is duplicated on all AMPs into 31 hash join partitions. The size of Spool 4 is estimated with no
confidence to be 7,868,024 rows (361,929,104 bytes). The estimated time for this step is 2.86 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from TBLDB.TAX_ACCT_FINANCIAL_TRANS_LIST in view VWDB.TAX_ACCT_FINANCIAL_TRANS_LIST by way of an all-rows
scan with no residual conditions into Spool 5 (all_amps) (compressed columns allowed) fanned out into 31 hash join partitions, which is built
locally on the AMPs. The size of Spool 5 is estimated with high confidence to be 348,210,376 rows (7,660,628,272 bytes). The estimated time for
this step is 46.28 seconds.
| | |
| | 5) | We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an all-rows scan, which is joined to Spool 5 (Last Use) by way of an all-rows
scan. Spool 4 and Spool 5 are joined using a hash join of 31 partitions, with a join condition of ("(SMRT_INTERNAL_ID = SMRT_INTERNAL_ID) AND
((SMRT_INTERNAL_ID = SMRT_INTERNAL_ID) AND (SMRT_INTERNAL_ID = SMRT_INTERNAL_ID ))"). The result goes into Spool 6 (all_amps) (compressed columns
allowed), which is duplicated on all AMPs. The size of Spool 6 is estimated with no confidence to be 312,817,572 rows (9,384,527,160 bytes). The
estimated time for this step is 42.27 seconds.
| |
| | 6) | We do an all-AMPs JOIN step from TBLDB.TAX_ACCOUNT_LIST in view VWDB.TAX_ACCOUNT_LIST by way of a RowHash match scan with no residual
conditions, which is joined to TBLDB.VIEW2 in view VWDB.VIEW2 by way of a RowHash match scan with no residual conditions. TBLDB.TAX_ACCOUNT_LIST
and TBLDB.VIEW2 are left outer joined using a merge join, with a join condition of ("(TBLDB.TAX_ACCOUNT_LIST.CR_NUM = TBLDB.VIEW2.CR_NUM) AND
((TBLDB.TAX_ACCOUNT_LIST.ACCT_TYPE_CD = TBLDB.VIEW2.ACCT_TYPE_CD) AND (TBLDB.TAX_ACCOUNT_LIST.SMRT_INTERNAL_ID = TBLDB.VIEW2.SMRT_INTERNAL_ID
))"). The input table TBLDB.VIEW2 will not be cached in memory. The result goes into Spool 7 (all_amps) (compressed columns allowed), which is
built locally on the AMPs. The size of Spool 7 is estimated with low confidence to be 98,979,191 rows (4,652,021,977 bytes). The estimated time
for this step is 46.97 seconds.
| |
| | 7) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an all-rows scan, which is joined to Spool 7 (Last Use) by way of an all-
rows scan. Spool 6 and Spool 7 are joined using a product join, with a join condition of ("((PRD_STATUS_CD >= 7) OR ((PRD_STATUS_CD <= 5) OR
((DW_VER_END_DT <= DATE '9999-12-30') OR ((DW_VER_END_DT > DATE '9999-12-31') OR ((ACCT_TYPE_CD <= 99) OR ((ACCT_TYPE_CD >= 101) OR
((TAX_PRD_END_DT <= DATE '9999-12-30') OR (TAX_PRD_END_DT > DATE '9999-12-31')))))))) OR ((STAT_CD >= 2) OR ((STAT_CD <= 0) OR (PRIM_ID_IND =
'n')))"). The result goes into Spool 8 (all_amps) (compressed columns allowed), which is built locally on the AMPs. Then we do a SORT to order
Spool 8 by the hash code of (TBLDB.TAX_ACCOUNT_LIST.CR_NUM, TBLDB.TAX_ACCOUNT_LIST.ACCT_TYPE_CD, TBLDB.TAX_ACCOUNT_LIST.SMRT_INTERNAL_ID). The
result spool file will not be cached in memory. The size of Spool 8 is estimated with no confidence to be 365,027,473,255 rows
(23,361,758,288,320 bytes). The estimated time for this step is 3,576 hours and 35 minutes.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from TBLDB.TAX_ACCT_FINANCIAL_TRANSACTION in view VWDB.TAX_ACCT_FINANCIAL_TRANSACTION by way of an all-
rows scan with a condition of ("(NOT (TBLDB.TAX_ACCT_FINANCIAL_TRANSACTION in view VWDB.TAX_ACCT_FINANCIAL_TRANSACTION.CR_NUM IS NULL )) AND (NOT
(TBLDB.TAX_ACCT_FINANCIAL_TRANSACTION in view VWDB.TAX_ACCT_FINANCIAL_TRANSACTION.ACCT_TYPE_CD IS NULL ))") into Spool 9 (all_amps) (compressed
columns allowed), which is duplicated on all AMPs. Then we do a SORT to order Spool 9 by the hash code of
(TBLDB.TAX_ACCT_FINANCIAL_TRANSACTION.SMRT_INTERNAL_ID, TBLDB.TAX_ACCT_FINANCIAL_TRANSACTION.ACCT_TYPE_CD,
TBLDB.TAX_ACCT_FINANCIAL_TRANSACTION.CR_NUM). The result spool file will not be cached in memory. The size of Spool 9 is estimated with high
confidence to be 8,337,104,906 rows (291,798,671,710 bytes). The estimated time for this step is 1 hour and 34 minutes.
| | |
| | 8) | We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of a RowHash match scan, which is joined to Spool 9 (Last Use) by way of a
RowHash match scan. Spool 8 and Spool 9 are left outer joined using a merge join, with a join condition of ("(CR_NUM = CR_NUM) AND ((ACCT_TYPE_CD
= ACCT_TYPE_CD) AND (SMRT_INTERNAL_ID = SMRT_INTERNAL_ID ))"). The result goes into Spool 1 (group_amps), which is built locally on the AMPs.
The result spool file will not be cached in memory. The size of Spool 1 is estimated with no confidence to be 10,748,387,383,270 rows
(730,890,342,062,360 bytes). The estimated time for this step is 725 hours and 17 minutes.
| |
| | 9) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 4,301 hours and 54 minutes.
| |
| |
