 |
 |
Archives of the TeradataForum
Message Posted: Mon, 22 May 2006 @ 15:50:20 GMT
| Subj: | | Optimize query plan for 5-table join |
| |
| From: | | Sada S Shirol |
Hi Everyone: I am trying to optimize query plan for 5-table join. The query is a view, which de-normalizes the normalized structures for ease
of query by the users. All five tables have same Primary Index. Each table is a summary table for facts at week level. All five tables have about
50 million rows. Primary Index consists of 8 columns. Statistics have been collected on primary index and for all columns in PI (individual
columns and multi-columns)
Query with count (*) is giving the expected plan. However, when select all columns from summary tables, query plan is totally different and we
no longer see amp local joins. Explain shows redistribution and product joins for summary tables. Clearly this is non-performing query plan. Any
insight in explaining why the plan changes drastically when we select columns vs select count (*) and suggestion to get query plan similar to
count (*) ( if at all possible) is much appreciated. Following are the explain plans.
Thanks in advance.
Query plan for count (*)
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct MHPSMD."pseudo table" for read on a RowHash to prevent global deadlock for MHPSMD.STK.
| |
| | 2) | Next, we lock a distinct MHPSMD."pseudo table" for read on a RowHash to prevent global deadlock for MHPSMD.MKDN.
| |
| | 3) | We lock a distinct MHPSMD."pseudo table" for read on a RowHash to prevent global deadlock for MHPSMD.ORD.
| |
| | 4) | We lock a distinct MHPSMD."pseudo table" for read on a RowHash to prevent global deadlock for MHPSMD.RCPT.
| |
| | 5) | We lock a distinct MHPSMD."pseudo table" for read on a RowHash to prevent global deadlock for MHPSMD.SLS.
| |
| | 6) | We lock MHPSMD.STK for read, we lock MHPSMD.MKDN for read, we lock MHPSMD.ORD for read, we lock MHPSMD.RCPT for read, and we lock
MHPSMD.SLS for read.
| |
| | 7) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do a single-AMP ABORT test from DBC.DBase by way of the unique primary index.
| | |
| | |
| | 2) | We do a single-AMP UPDATE from DBC.SessionTbl by way of the primary index with no residual conditions.
| | |
| | |
| | 3) | We do an all-AMPs JOIN step from MHPSMD.RCPT by way of a RowHash match scan with no residual conditions, which is joined to MHPSMD.SLS by
way of a RowHash match scan with no residual conditions. MHPSMD.RCPT and MHPSMD.SLS are joined using a merge join, with a join condition of (
"(MHPSMD.SLS.OPDIV_DIVN_NBR = MHPSMD.RCPT.OPDIV_DIVN_NBR) AND ((MHPSMD.SLS.DEPT_NBR = MHPSMD.RCPT.DEPT_NBR) AND ((MHPSMD.SLS.CORP_DEPT_NBR =
MHPSMD.RCPT.CORP_DEPT_NBR) AND ((MHPSMD.SLS.LOC_DIM_ID = MHPSMD.RCPT.LOC_DIM_ID) AND ((MHPSMD.SLS.AMC_YEAR = MHPSMD.RCPT.AMC_YEAR) AND
((MHPSMD.SLS.AMC_PERIOD = MHPSMD.RCPT.AMC_PERIOD) AND ((MHPSMD.SLS.AMC_WEEK = MHPSMD.RCPT.AMC_WEEK) AND ((MHPSMD.SLS.LBL_NBR =
MHPSMD.RCPT.LBL_NBR) AND ((MHPSMD.SLS.GDBTRBST_SORT_ORDER = MHPSMD.RCPT.GDBTRBST_SORT_ORDER) AND (MHPSMD.SLS.VND_NUMERIC_DESC =
MHPSMD.RCPT.VND_NUMERIC_DESC )))))))))"). The input tables MHPSMD.RCPT and MHPSMD.SLS will not be cached in memory, but MHPSMD.RCPT is eligible
for synchronized scanning. The result goes into Spool 4 (all_amps), which is built locally on the AMPs. The result spool file will not be cached
in memory. The size of Spool 4 is estimated with low confidence to be 45,812,170 rows. The estimated time for this step is 18.08 seconds.
| | |
| | 8) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from MHPSMD.STK by way of a RowHash match scan with no residual conditions, which is joined to Spool 4 (Last
Use) by way of a RowHash match scan. MHPSMD.STK and Spool 4 are joined using a merge join, with a join condition of ("(VND_NUMERIC_DESC =
MHPSMD.STK.VND_NUMERIC_DESC) AND ((GDBTRBST_SORT_ORDER = MHPSMD.STK.GDBTRBST_SORT_ORDER) AND ((LBL_NBR = MHPSMD.STK.LBL_NBR) AND ((AMC_WEEK =
MHPSMD.STK.AMC_WEEK) AND ((AMC_PERIOD = MHPSMD.STK.AMC_PERIOD) AND ((AMC_YEAR = MHPSMD.STK.AMC_YEAR) AND ((LOC_DIM_ID = MHPSMD.STK.LOC_DIM_ID) AND
((CORP_DEPT_NBR = MHPSMD.STK.CORP_DEPT_NBR) AND ((DEPT_NBR = MHPSMD.STK.DEPT_NBR) AND ((OPDIV_DIVN_NBR = MHPSMD.STK.OPDIV_DIVN_NBR) AND
((MHPSMD.STK.OPDIV_DIVN_NBR = OPDIV_DIVN_NBR) AND ((MHPSMD.STK.DEPT_NBR = DEPT_NBR) AND ((MHPSMD.STK.CORP_DEPT_NBR = CORP_DEPT_NBR) AND
((MHPSMD.STK.LOC_DIM_ID = LOC_DIM_ID) AND ((MHPSMD.STK.AMC_YEAR = AMC_YEAR) AND ((MHPSMD.STK.AMC_PERIOD = AMC_PERIOD) AND ((MHPSMD.STK.AMC_WEEK =
AMC_WEEK) AND ((MHPSMD.STK.LBL_NBR = LBL_NBR) AND ((MHPSMD.STK.GDBTRBST_SORT_ORDER = GDBTRBST_SORT_ORDER) AND (MHPSMD.STK.VND_NUMERIC_DESC =
VND_NUMERIC_DESC )))))))))))))))))))"). The input table MHPSMD.STK will not be cached in memory, but it is eligible for synchronized scanning.
The result goes into Spool 5 (all_amps), which is built locally on the AMPs. The result spool file will not be cached in memory. The size of
Spool 5 is estimated with low confidence to be 45,812,170 rows. The estimated time for this step is 16.96 seconds.
| | |
| | |
| | 2) | We do an all-AMPs JOIN step from MHPSMD.MKDN by way of a RowHash match scan with no residual conditions, which is joined to MHPSMD.ORD by
way of a RowHash match scan with no residual conditions. MHPSMD.MKDN and MHPSMD.ORD are joined using a merge join, with a join condition of (
"(MHPSMD.ORD.OPDIV_DIVN_NBR = MHPSMD.MKDN.OPDIV_DIVN_NBR) AND ((MHPSMD.ORD.DEPT_NBR = MHPSMD.MKDN.DEPT_NBR) AND ((MHPSMD.ORD.CORP_DEPT_NBR =
MHPSMD.MKDN.CORP_DEPT_NBR) AND ((MHPSMD.ORD.LOC_DIM_ID = MHPSMD.MKDN.LOC_DIM_ID) AND ((MHPSMD.ORD.AMC_YEAR = MHPSMD.MKDN.AMC_YEAR) AND
((MHPSMD.ORD.AMC_PERIOD = MHPSMD.MKDN.AMC_PERIOD) AND ((MHPSMD.ORD.AMC_WEEK = MHPSMD.MKDN.AMC_WEEK) AND ((MHPSMD.ORD.LBL_NBR =
MHPSMD.MKDN.LBL_NBR) AND ((MHPSMD.ORD.GDBTRBST_SORT_ORDER = MHPSMD.MKDN.GDBTRBST_SORT_ORDER) AND (MHPSMD.ORD.VND_NUMERIC_DESC =
MHPSMD.MKDN.VND_NUMERIC_DESC )))))))))"). The input tables MHPSMD.MKDN and MHPSMD.ORD will not be cached in memory, but MHPSMD.MKDN is eligible
for synchronized scanning. The result goes into Spool 6 (all_amps), which is built locally on the AMPs. The result spool file will not be cached
in memory. The size of Spool 6 is estimated with low confidence to be 45,812,170 rows. The estimated time for this step is 11.87 seconds.
| | |
| | 9) | We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of a RowHash match scan, which is joined to Spool 6 (Last Use) by way of a
RowHash match scan. Spool 5 and Spool 6 are joined using a merge join, with a join condition of ("(VND_NUMERIC_DESC = VND_NUMERIC_DESC) AND
((GDBTRBST_SORT_ORDER = GDBTRBST_SORT_ORDER) AND ((LBL_NBR = LBL_NBR) AND ((AMC_WEEK = AMC_WEEK) AND ((AMC_PERIOD = AMC_PERIOD) AND ((AMC_YEAR =
AMC_YEAR) AND ((LOC_DIM_ID = LOC_DIM_ID) AND((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((OPDIV_DIVN_NBR = OPDIV_DIVN_NBR) AND
((OPDIV_DIVN_NBR = OPDIV_DIVN_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((LOC_DIM_ID = LOC_DIM_ID) AND ((AMC_YEAR =
AMC_YEAR) AND ((AMC_PERIOD = AMC_PERIOD) AND ((AMC_WEEK = AMC_WEEK) AND ((LBL_NBR = LBL_NBR) AND ((GDBTRBST_SORT_ORDER = GDBTRBST_SORT_ORDER) AND
((VND_NUMERIC_DESC = VND_NUMERIC_DESC) AND ((VND_NUMERIC_DESC = VND_NUMERIC_DESC) AND ((GDBTRBST_SORT_ORDER = GDBTRBST_SORT_ORDER) AND ((LBL_NBR =
LBL_NBR) AND ((AMC_WEEK = AMC_WEEK) AND ((AMC_PERIOD = AMC_PERIOD) AND ((AMC_YEAR = AMC_YEAR) AND ((LOC_DIM_ID = LOC_DIM_ID) AND ((CORP_DEPT_NBR =
CORP_DEPT_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((OPDIV_DIVN_NBR = OPDIV_DIVN_NBR) AND ((VND_NUMERIC_DESC = VND_NUMERIC_DESC) AND
((GDBTRBST_SORT_ORDER = GDBTRBST_SORT_ORDER) AND ((LBL_NBR = LBL_NBR) AND ((AMC_WEEK= AMC_WEEK) AND ((AMC_PERIOD = AMC_PERIOD) AND ((AMC_YEAR =
AMC_YEAR) AND ((LOC_DIM_ID = LOC_DIM_ID) AND ((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((OPDIV_DIVN_NBR = OPDIV_DIVN_NBR)
AND ((OPDIV_DIVN_NBR = OPDIV_DIVN_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((LOC_DIM_ID = LOC_DIM_ID) AND
((AMC_YEAR = AMC_YEAR) AND ((AMC_PERIOD = AMC_PERIOD) AND ((AMC_WEEK = AMC_WEEK) AND ((LBL_NBR =LBL_NBR) AND ((GDBTRBST_SORT_ORDER =
GDBTRBST_SORT_ORDER) AND ((VND_NUMERIC_DESC = VND_NUMERIC_DESC) AND ((OPDIV_DIVN_NBR = OPDIV_DIVN_NBR) AND ((DEPT_NBR = DEPT_NBR) AND
((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((LOC_DIM_ID = LOC_DIM_ID) AND ((AMC_YEAR = AMC_YEAR) AND ((AMC_PERIOD = AMC_PERIOD) AND ((AMC_WEEK =
AMC_WEEK) AND ((LBL_NBR = LBL_NBR) AND ((GDBTRBST_SORT_ORDER = GDBTRBST_SORT_ORDER) AND (VND_NUMERIC_DESC = VND_NUMERIC_DESC
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))"). The result goes into Spool 3 (all_amps), which is built locally on the AMPs. The
size of Spool 3 is estimated with low confidence to be 45,812,170 rows. The estimated time for this step is 7.00 seconds.
| |
| | 10) | We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by way of an all-rows scan. Aggregate Intermediate Results are computed
globally, then placed in Spool 7. The size of Spool 7 is estimated with high confidence to be 1 row. The estimated time for this step is 0.63
seconds.
| |
| | 11) | We do an all-AMPs RETRIEVE step from Spool 7 (Last Use) by way of an all-rows scan into Spool 1 (group_amps), which is built locally on
the AMPs. The size of Spool 1 is estimated with high confidence to be 1 row. The estimated time for this step is 0.03 seconds.
| |
| | 12) | 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 contents of Spool 1 are sent back to the user as the result of statement
2.
| |
Explain Plan when columns are selected:
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct MHPSMD."pseudo table" for read on a RowHash to prevent global deadlock for MHPSMD.STK.
| |
| | 2) | Next, we lock a distinct MHPSMD."pseudo table" for read on a RowHash to prevent global deadlock for MHPSMD.MKDN.
| |
| | 3) | We lock a distinct MHPSMD."pseudo table" for read on a RowHash to prevent global deadlock for MHPSMD.ORD.
| |
| | 4) | We lock a distinct MHPSMD."pseudo table" for read on a RowHash to prevent global deadlock for MHPSMD.RCPT.
| |
| | 5) | We lock a distinct MHPSMD."pseudo table" for read on a RowHash to prevent global deadlock for MHPSMD.SLS.
| |
| | 6) | We lock MHPSMD.STK for read, we lock MHPSMD.MKDN for read, we lock MHPSMD.ORD for read, we lock MHPSMD.RCPT for read, and we lock
MHPSMD.SLS for read.
| |
| | 7) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do a single-AMP ABORT test from DBC.DBase by way of the unique primary index.
| | |
| | |
| | 2) | We do a single-AMP UPDATE from DBC.SessionTbl by way of the primary index with no residual conditions.
| | |
| | |
| | 3) | We do an all-AMPs RETRIEVE step from MHPSMD.ORD by way of an all-rows scan with no residual conditions into Spool 2 (all_amps), which is
redistributed by hash code to all AMPs. Then we do a SORT to order Spool 2 by row hash. The result spool file will not be cached in memory. The
size of Spool 2 is estimated with high confidence to be 45,812,170 rows. The estimated time for this step is 14.45 seconds.
| | |
| | |
| | 4) | We do an all-AMPs RETRIEVE step from MHPSMD.SLS by way of an all-rows scan with no residual conditions into Spool 3 (all_amps), which is
redistributed by hash code to all AMPs. Then we do a SORT to order Spool 3 by row hash. The result spool file will not be cached in memory. The
size of Spool 3 is estimated with high confidence to be 45,812,170 rows. The estimated time for this step is 18.05 seconds.
| | |
| | |
| | 5) | We do an all-AMPs RETRIEVE step from MHPSMD.MKDN by way of an all-rows scan with no residual conditions into Spool 4 (all_amps), which is
redistributed by hash code to all AMPs. Then we do a SORT to order Spool 4 by row hash. The result spool file will not be cached in memory. The
size of Spool 4 is estimated with high confidence to be 45,812,170 rows. The estimated time for this step is 16.73 seconds.
| | |
| | |
| | 6) | We do an all-AMPs RETRIEVE step from MHPSMD.RCPT by way of an all-rows scan with no residual conditions into Spool 5 (all_amps), which is
redistributed by hash code to all AMPs. Then we do a SORT to order Spool 5 by row hash. The result spool file will not be cached in memory. The
size of Spool 5 is estimated with high confidence to be 45,812,170 rows. The estimated time for this step is 19.34 seconds.
| | |
| | |
| | 7) | We do an all-AMPs RETRIEVE step from MHPSMD.STK by way of an all-rows scan with no residual conditions into Spool 6 (all_amps), which is
redistributed by hash code to all AMPs. Then we do a SORT to order Spool 6 by row hash. The result spool file will not be cached in memory. The
size of Spool 6 is estimated with high confidence to be 45,812,170 rows. The estimated time for this step is 20.33 seconds.
| | |
| | 8) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a RowHash match scan, which is joined to Spool 5 (Last Use) by way of a
RowHash match scan. Spool 4 and Spool 5 are joined using a merge join, with a join condition of ( "(OPDIV_DIVN_NBR = OPDIV_DIVN_NBR) AND
((DIVN_NBR = DIVN_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((LOC_DIM_ID = LOC_DIM_ID) AND ((AMC_YEAR = AMC_YEAR)
AND ((AMC_PERIOD = AMC_PERIOD) AND (AMC_WEEK = AMC_WEEK )))))))"). The result goes into Spool 7 (all_amps), which is built locally on the AMPs.
The result spool file will not be cached in memory. The size of Spool 7 is estimated with low confidence to be 45,812,170 rows. The estimated
time for this step is 6.61 seconds.
| | |
| | |
| | 2) | We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan, which is joined to Spool 3 (Last Use) by way of a
RowHash match scan. Spool 2 and Spool 3 are joined using a merge join, with a join condition of ( "(OPDIV_DIVN_NBR = OPDIV_DIVN_NBR) AND
((DIVN_NBR = DIVN_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((LOC_DIM_ID = LOC_DIM_ID) AND ((AMC_YEAR = AMC_YEAR)
AND ((AMC_PERIOD = AMC_PERIOD) AND (AMC_WEEK = AMC_WEEK )))))))"). The result goes into Spool 8 (all_amps), which is built locally on the AMPs.
The result spool file will not be cached in memory. The size of Spool 8 is estimated with low confidence to be 45,812,170 rows. The estimated
time for this step is 6.61 seconds.
| | |
| | 9) | We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of a RowHash match scan, which is joined to Spool 7 (Last Use) by way of a
RowHash match scan. Spool 6 and Spool 7 are joined using a merge join, with a join condition of ("(OPDIV_DIVN_NBR = OPDIV_DIVN_NBR) AND
((DIVN_NBR = DIVN_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((LOC_DIM_ID = LOC_DIM_ID) AND ((AMC_YEAR = AMC_YEAR)
AND ((AMC_PERIOD = AMC_PERIOD) AND ((AMC_WEEK =AMC_WEEK) AND ((AMC_WEEK = AMC_WEEK) AND ((AMC_PERIOD = AMC_PERIOD) AND ((AMC_YEAR = AMC_YEAR) AND
((LOC_DIM_ID = LOC_DIM_ID) AND ((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((DIVN_NBR = DIVN_NBR) AND (OPDIV_DIVN_NBR =
OPDIV_DIVN_NBR )))))))))))))))"). The result goes into Spool 9 (all_amps), which is built locally on the AMPs. The result spool file will not be
cached in memory. The size of Spool 9 is estimated with low confidence to be 45,812,170 rows. The estimated time for this step is 8.54 seconds.
| |
| | 10) | We execute the following steps in parallel.
| |
| | |
| | 1) | 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 joined using a merge join, with a join condition of ( "(OPDIV_DIVN_NBR = OPDIV_DIVN_NBR) AND
((DIVN_NBR = DIVN_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((LOC_DIM_ID = LOC_DIM_ID) AND ((AMC_YEAR = AMC_YEAR)
AND ((AMC_PERIOD = AMC_PERIOD) AND ((AMC_WEEK =AMC_WEEK) AND ((AMC_WEEK = AMC_WEEK) AND ((AMC_PERIOD = AMC_PERIOD) AND ((AMC_YEAR = AMC_YEAR) AND
((LOC_DIM_ID = LOC_DIM_ID) AND ((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((DIVN_NBR = DIVN_NBR) AND ((OPDIV_DIVN_NBR =
OPDIV_DIVN_NBR) AND ((AMC_WEEK = AMC_WEEK) AND ((AMC_PERIOD = AMC_PERIOD) AND ((AMC_YEAR = AMC_YEAR) AND ((LOC_DIM_ID = LOC_DIM_ID) AND
((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((DIVN_NBR = DIVN_NBR) AND ((OPDIV_DIVN_NBR = OPDIV_DIVN_NBR) AND ((OPDIV_DIVN_NBR
= OPDIV_DIVN_NBR) AND ((DIVN_NBR = DIVN_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((LOC_DIM_ID = LOC_DIM_ID) AND
((AMC_YEAR = AMC_YEAR) AND ((AMC_PERIOD = AMC_PERIOD) AND ((AMC_WEEK = AMC_WEEK) AND ((AMC_WEEK = AMC_WEEK) AND ((AMC_PERIOD = AMC_PERIOD) AND
((AMC_YEAR = AMC_YEAR) AND ((LOC_DIM_ID = LOC_DIM_ID) AND ((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((DIVN_NBR = DIVN_NBR)
AND ((OPDIV_DIVN_NBR = OPDIV_DIVN_NBR) AND ((AMC_WEEK = AMC_WEEK) AND ((AMC_PERIOD = AMC_PERIOD) AND ((AMC_YEAR = AMC_YEAR) AND ((LOC_DIM_ID =
LOC_DIM_ID) AND ((CORP_DEPT_NBR = CORP_DEPT_NBR) AND ((DEPT_NBR = DEPT_NBR) AND ((DIVN_NBR = DIVN_NBR) AND (OPDIV_DIVN_NBR = OPDIV_DIVN_NBR
)))))))))))))))))))))))))))))))))))))))))))))))"). The result goes into Spool 10 (all_amps), which is duplicated on all AMPs. The result spool
file will not be cached in memory. The size of Spool 10 is estimated with low confidence to be 8,246,190,600 rows. The estimated time for this
step is 3 minutes and 33 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from MHPSMD.SALES_DIVN_LOC_CDV_WK by way of an all-rows scan with no residual conditions into Spool 11
(all_amps), which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The
result spool file will not be cached in memory. The size of Spool 11 is estimated with high confidence to be 45,812,170 rows. The estimated time
for this step is 15.71 seconds.
| | |
| | |
| | 3) | We do an all-AMPs RETRIEVE step from MHPSMD.ON_ORDER_DIVN_LOC_CDV_WK by way of an all-rows scan with no residual conditions into Spool 12
(all_amps), which is duplicated on all AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The
result spool file will not be cached in memory. The size of Spool 12 is estimated with high confidence to be 8,246,190,600 rows. The estimated
time for this step is 5 minutes and 38 seconds.
| | |
| | |
| | 4) | We do an all-AMPs RETRIEVE step from MHPSMD.STK_DIVN_LOC_CDV_WK by way of an all-rows scan with no residual conditions into Spool 13
(all_amps), which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The
result spool file will not be cached in memory. The size of Spool 13 is estimated with high confidence to be 45,812,170 rows. The estimated time
for this step is 21.43 seconds.
| | |
| | |
| | 5) | We do an all-AMPs RETRIEVE step from MHPSMD.MARKDOWN_DIVN_LOC_CDV_WK by way of an all-rows scan with no residual conditions into Spool 14
(all_amps), which is duplicated on all AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The
result spool file will not be cached in memory. The size of Spool 14 is estimated with high confidence to be 8,246,190,600 rows. The estimated
time for this step is 20 minutes and 31 seconds.
| | |
| | 11) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of an all-rows scan, which is joined to Spool 11 (Last Use) by way of an all-
rows scan. Spool 10 and Spool 11 are joined using a product join, with a join condition of ("(1=1)"). The result goes into Spool 15 (all_amps),
which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 15 is estimated with low confidence to
be *** rows. The estimated time for this step is 123,831 hours and 8 minutes.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from MHPSMD.RCPT_DIVN_LOC_CDV_WK by way of an all-rows scan with no residual conditions into Spool 16
(all_amps), which is duplicated on all AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The
result spool file will not be cached in memory. The size of Spool 16 is estimated with high confidence to be 8,246,190,600 rows. The estimated
time for this step is 47 minutes and 36 seconds.
| | |
| | |
| | 3) | We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of an all-rows scan, which is joined to Spool 13 (Last Use) by way of an all-
rows scan. Spool 12 and Spool 13 are joined using a product join, with a join condition of ("(1=1)"). The result goes into Spool 17 (all_amps),
which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 17 is estimated with high confidence
to be *** rows. The estimated time for this step is 173,802 hours and 30 minutes.
| | |
| | 12) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 14 (Last Use) by way of an all-rows scan, which is joined to Spool 15 (Last Use) by way of an all-
rows scan. Spool 14 and Spool 15 are joined using a product join, with a join condition of ("(1=1)"). The result goes into Spool 18 (all_amps),
which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 18 is estimated with low confidence to
be *** rows. The estimated time for this step is 7,962,518,660,107 hours and 43 minutes.
| | |
| | |
| | 2) | We do an all-AMPs JOIN step from Spool 16 (Last Use) by way of an all-rows scan, which is joined to Spool 17 (Last Use) by way of an all-
rows scan. Spool 16 and Spool 17 are joined using a product join, with a join condition of ("(1=1)"). The result goes into Spool 19 (all_amps),
which is duplicated on all AMPs. The result spool file will not be cached in memory. The size of Spool 19 is estimated with high confidence to be
*** rows. The estimated time for this step is *** hours.
| | |
| | 13) | We do an all-AMPs JOIN step from Spool 18 (Last Use) by way of an all-rows scan, which is joined to Spool 19 (Last Use) by way of an all-
rows scan. Spool 18 and Spool 19 are joined using a product join, with a join condition of ("(1=1)"). 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 low confidence to
be *** rows. The estimated time for this step is *** hours.
| |
| | 14) | 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 contents of Spool 1 are sent back to the user as the result of statement
2.
| |
| |
