 |
 |
Archives of the TeradataForum
Message Posted: Wed, 24 Apr 2013 @ 09:49:22 GMT
| Subj: | | Tuning the Query to Better Performance |
| |
| From: | | Anomy Anom |
<-- Anonymously Posted: Tuesday, April 23, 2013 18:40 -->
Hi Experts,
Could you suggest any performance can be improved on this query
Currently this query running average 1 min 15 seconds our users needed this to be run in less than 15 seconds
Up dated all stats but not much improvement.
Appreciate your thoughts Thank you.
Attached Explain plan.
Explain SELECT
CASE WHEN
( POLICY.Carrier_Admin_System ) LIKE 'AS400%' THEN POLICY.FULL_Policy_NO
ELSE ( POLICY.Policy_No ) END,
SALES_AMT.ACCT_DT,
SUM(CASE WHEN POLICY.Eff_Dt > '2011/01/01' AND
SALES_AMT.SALES_AMT=SALES_AMT.SALES_AMT_TOTAL THEN SALES_AMT.SHR_PCT WHEN
POLICY.Eff_Dt > '2011/01/01' AND SALES_AMT.SALES_AMT_TOTAL=0 THEN
SALES_AMT.SHR_PCT*-1 ELSE 0 END) ,
sum(SALES_AMT.SALES_AMT),
SALES_HIERARCHY.APPOINTED_PRODUCER_NM,
SALES_HIERARCHY.SHARE_PCT
FROM
(
SELECT
SHA.SALES_HIER_UID,
SHA.APPOINTED_PRODUCER_SK,
SHA.POLICY_SK,
SHA.COV_COMP_SK,
SHA.ACCT_DT,
P.POLICY_NO,
SHA.SHR_PCT,
CASE WHEN SHA.SALES_HIER_UID = 2152938 AND SHA.ACCT_DT = {D'2013-01-05'} THEN 0--AB02848570
WHEN SHA.SALES_HIER_UID = 2174538 AND SHA.ACCT_DT = {D'2013-01-06'} THEN 0--AB02924660
WHEN SHA.SALES_HIER_UID = 2180738 AND SHA.ACCT_DT = {D'2013-01-12'} THEN 0--AB02931790
WHEN SHA.SALES_HIER_UID = 2174186 AND SHA.ACCT_DT = {D'2013-01-05'} THEN 0--AB02982620
WHEN SHA.SALES_HIER_UID = 2171736 AND SHA.ACCT_DT = {D'2013-01-02'} THEN 0--AB03030280
WHEN SHA.SALES_HIER_UID = 2174214 AND SHA.ACCT_DT = {D'2013-01-05'} THEN 0--AB03268180
WHEN SHA.SALES_HIER_UID = 2152339 AND SHA.ACCT_DT = {D'2013-01-15'} THEN 0--BL06323290
WHEN SHA.SALES_HIER_UID = 2161096 AND SHA.ACCT_DT = {D'2013-01-15'} THEN 0--IL02525800
WHEN SHA.SALES_HIER_UID = 2180907 AND SHA.ACCT_DT = {D'2013-01-12'} THEN 0--AB03280190
WHEN SHA.SALES_HIER_UID = 2195561 AND SHA.ACCT_DT = {D'2013-01-30'} THEN 0--AB03281080
WHEN SHA.SALES_HIER_UID = 1802275 AND SHA.ACCT_DT = {D'2013-01-04'} AND
SHA.COV_COMP_SK = 7056389 THEN 0--IL02482480
WHEN SHA.SALES_HIER_UID = 2194796 AND SHA.ACCT_DT = {D'2013-02-15'} THEN 0--AB03088400
WHEN SHA.SALES_HIER_UID = 2174087 AND SHA.ACCT_DT = {D'2013-02-05'} THEN 0--AB03099350
WHEN SHA.SALES_HIER_UID = 2194207 AND SHA.ACCT_DT = {D'2013-02-03'} THEN 0--AB03232350
WHEN SHA.SALES_HIER_UID = 2178896 AND SHA.ACCT_DT = {D'2013-02-03'} THEN 0--BL28905900
WHEN SHA.SALES_HIER_UID = 1305202 AND SHA.ACCT_DT = {D'2013-02-03'} THEN 0--IL02479250
WHEN SHA.SALES_HIER_UID = 1305202 AND SHA.ACCT_DT = {D'2013-03-01'} THEN 0--IL02479250
WHEN SHA.SALES_HIER_UID = 1647974 AND SHA.ACCT_DT = {D'2013-02-03'} THEN 0--IL02499920
WHEN SHA.SALES_HIER_UID = 1647974 AND SHA.ACCT_DT = {D'2013-03-01'} THEN 0--IL02499920
WHEN SHA.SALES_HIER_UID = 2172860 AND SHA.ACCT_DT = {D'2013-02-07'} THEN 0--IL02561120
ELSE
SHA.SALES_AMT
END AS SALES_AMT,
SHA.SALES_AMT_TOTAL,
SALES_AMT_COUNT
FROM
(SELECT
PROD_V.DS_ENH_VW1_SALES_HIER_AMT.SALES_HIER_UID,
PROD_V.DS_ENH_VW1_SALES_HIER_AMT.POLICY_SK,
PROD_V.DS_ENH_VW1_SALES_HIER_AMT.COV_COMP_SK,
PROD_V.DS_ENH_VW1_SALES_HIER_AMT.Appointed_Producer_Sk,
PROD_V.DS_ENH_VW1_SALES_HIER_AMT.ACCT_DT,
SALES_AMT,
CASE WHEN PROD_V.DS_ENH_VW1_SALES_HIER_AMT.SHR_PCT > 1 THEN
PROD_V.DS_ENH_VW1_SALES_HIER_AMT.SHR_PCT/100 ELSE
PROD_V.DS_ENH_VW1_SALES_HIER_AMT.SHR_PCT END as SHR_PCT,
Sum(PROD_V.DS_ENH_VW1_SALES_HIER_AMT.SALES_AMT) Over (PARTITION BY
PROD_V.DS_ENH_VW1_SALES_HIER_AMT.SALES_HIER_UID order by
PROD_V.DS_ENH_VW1_SALES_HIER_AMT.ACCT_DT rows unbounded preceding) AS
SALES_AMT_TOTAL,
Sum(PROD_V.DS_ENH_VW1_SALES_HIER_AMT.SALES_AMT) Over (PARTITION BY
SALES_HIER_UID,POLICY_SK,COV_COMP_SK order by ACCT_DT rows unbounded
preceding) AS SALES_AMT_COUNT
FROM PROD_V.DS_ENH_VW1_SALES_HIER_AMT
where (Sales_Hier_UID,Prdcr_Lvl_Num) in (Sel
Sales_Hier_UID,max(Prdcr_Lvl_Num) from Prod_V.ds_enh_vw_sales_hier_amt group
by 1)
GROUP BY 1,2,3,4,5,6,7 )SHA
INNER JOIN (SELECT *
FROM PROD_V.DS_VW_POLICY
WHERE CURRENT_REC_IND=1)P
ON SHA.POLICY_SK = P.POLICY_SK
) SALES_AMT RIGHT OUTER JOIN PROD_V.DS_ENH_VW_SALES_HIER SALES_HIERARCHY
ON (SALES_HIERARCHY.Sales_Hier_UID=SALES_AMT.SALES_HIER_UID and
SALES_HIERARCHY.APPOINTED_PRODUCER_SK=SALES_AMT.APPOINTED_PRODUCER_SK)
RIGHT OUTER JOIN PROD_V.DS_VW_POLICY POLICY ON
(POLICY.Policy_SK=SALES_HIERARCHY.POLICY_SK)
WHERE
( POLICY.Current_Rec_Ind=1 )
AND ( POLICY.Status_Cd<>111 )
AND
(
CASE WHEN
( POLICY.Carrier_Admin_System ) LIKE 'AS400%' THEN POLICY.FULL_Policy_NO
ELSE ( POLICY.Policy_No ) END IN ( 'ab03257680' )
AND
SALES_AMT.ACCT_DT > {d '2011-01-01'}
)
GROUP BY
1,
2,
5,
6
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock prod_enh_t.ENH_SALES_HIER_AMT for access, we lock prod_enh_t.ENH_SALES_HIER in view PROD_V.DS_ENH_VW_SALES_HIER for access,
we lock Prod_t.DS_SALES_HIER_AMT for access, we lock Prod_t.DS_SALES_HIER in view PROD_V.DS_ENH_VW_SALES_HIER for access, we lock
PROD_T.DS_DISTRIBUTION_AGREEMENT for access, we lock PROD_T.DS_COVERAGE_COMPONENT for access, we lock PROD_T.DS_APPOINTED_PRODUCER for access, we
lock PROD_T.DS_ROLE_PLAYER for access, we lock PROD_T.DS_POLICY for access, and we lock PROD_T.DS_LU_DISTCHAN for access.
| |
| | 2) | Next, we do an all-AMPs RETRIEVE step from prod_enh_t.ENH_SALES_HIER in view PROD_V.DS_ENH_VW_SALES_HIER by way of an all-rows scan with a
condition of ("((( CASE WHEN (NOT (prod_enh_t.ENH_SALES_HIER in view PROD_V.DS_ENH_VW_SALES_HIER.ENH_REC_STAT IS NULL )) THEN
(prod_enh_t.ENH_SALES_HIER in view PROD_V.DS_ENH_VW_SALES_HIER.ENH_REC_STAT) ELSE (1) END ))= 1) AND (NOT (prod_enh_t.ENH_SALES_HIER in view
PROD_V.DS_ENH_VW_SALES_HIER.SALES_HIER_UID IS NULL ))") into Spool 5 (all_amps), which is duplicated on all AMPs. The size of Spool 5 is
estimated with low confidence to be 96 rows (2,208 bytes). The estimated time for this step is 0.03 seconds.
| |
| | 3) | We do an all-AMPs JOIN step from Prod_t.DS_SALES_HIER in view PROD_V.DS_ENH_VW_SALES_HIER by way of an all-rows scan with no residual
conditions, which is joined to Spool 5 (Last Use) by way of an all-rows scan. Prod_t.DS_SALES_HIER and Spool 5 are left outer joined using a
product join, with condition(s) used for non-matching on left table ("NOT (Prod_t.DS_SALES_HIER.SALES_HIER_UID IS NULL)"), with a join condition
of ("Prod_t.DS_SALES_HIER.SALES_HIER_UID = SALES_HIER_UID"). The result goes into Spool 6 (all_amps), which is built locally on the AMPs. The
size of Spool 6 is estimated with low confidence to be 2,979,680 rows (688,306,080 bytes). The estimated time for this step is 1.47 seconds.
| |
| | 4) | We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of an all-rows scan with a condition of ("(( CASE WHEN (NOT (ENH_CORCT_CD IS
NULL )) THEN (ENH_CORCT_CD) ELSE ('A') END ))= 'A'") into Spool 4 (all_amps), which is built locally on the AMPs. The size of Spool 4 is estimated
with low confidence to be 2,979,680 rows (1,325,957,600 bytes). The estimated time for this step is 1.40 seconds.
| |
| | 5) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from prod_enh_t.ENH_SALES_HIER in view PROD_V.DS_ENH_VW_SALES_HIER by way of an all-rows scan with a
condition of ("(prod_enh_t.ENH_SALES_HIER in view PROD_V.DS_ENH_VW_SALES_HIER.ENH_REC_STAT = 1) AND ((prod_enh_t.ENH_SALES_HIER in view
PROD_V.DS_ENH_VW_SALES_HIER.ENH_CORCT_CD = 'I') AND ((NOT (prod_enh_t.ENH_SALES_HIER in view PROD_V.DS_ENH_VW_SALES_HIER.WRIT_APPT_PRDCR_SK IS
NULL )) AND (NOT (prod_enh_t.ENH_SALES_HIER in view PROD_V.DS_ENH_VW_SALES_HIER.SALES_HIER_UID IS NULL ))))") into Spool 4 (all_amps), which is
built locally on the AMPs. The size of Spool 4 is estimated with low confidence to be 2,979,681 rows (1,325,958,045 bytes). The estimated time
for this step is 0.03 seconds.
| | |
| | |
| | 2) | We do an all-AMPs JOIN step from prod_enh_t.ENH_SALES_HIER_AMT by way of a RowHash match scan with a condition of ("((( CASE WHEN (NOT
(prod_enh_t.ENH_SALES_HIER_AMT.ENH_REC_STAT IS NULL )) THEN (prod_enh_t.ENH_SALES_HIER_AMT.ENH_REC_STAT) ELSE (1) END ))= 1) AND (NOT
(prod_enh_t.ENH_SALES_HIER_AMT.SALES_HIER_UID IS NULL ))"), which is joined to Prod_t.DS_SALES_HIER_AMT by way of a RowHash match scan with no
residual conditions. prod_enh_t.ENH_SALES_HIER_AMT and Prod_t.DS_SALES_HIER_AMT are right outer joined using a merge join, with condition(s) used
for non-matching on right table ("(NOT (Prod_t.DS_SALES_HIER_AMT.SALES_HIER_UID IS NULL )) AND ((NOT (Prod_t.DS_SALES_HIER_AMT.ACCT_DT IS NULL ))
AND ((NOT (Prod_t.DS_SALES_HIER_AMT.COV_COMP_SK IS NULL )) AND ((NOT (Prod_t.DS_SALES_HIER_AMT.WRIT_APPT_PRDCR_SK IS NULL )) AND (NOT
(Prod_t.DS_SALES_HIER_AMT.POLICY_SK IS NULL )))))"), with a join condition of ("((( CASE WHEN (NOT (Prod_t.DS_SALES_HIER_AMT.PRDCR_LVL_NUM IS NULL
)) THEN (Prod_t.DS_SALES_HIER_AMT.PRDCR_LVL_NUM) ELSE (0) END ))= (( CASE WHEN (NOT (prod_enh_t.ENH_SALES_HIER_AMT.PRDCR_LVL_NUM IS NULL )) THEN
(prod_enh_t.ENH_SALES_HIER_AMT.PRDCR_LVL_NUM) ELSE (0) END ))) AND (((( CASE WHEN (NOT (Prod_t.DS_SALES_HIER_AMT.DSTRB_ROLE_TYP IS NULL )) THEN
(Prod_t.DS_SALES_HIER_AMT.DSTRB_ROLE_TYP) ELSE (0) END ))= (( CASE WHEN (NOT (prod_enh_t.ENH_SALES_HIER_AMT.DSTRB_ROLE_TYP IS NULL )) THEN
(prod_enh_t.ENH_SALES_HIER_AMT.DSTRB_ROLE_TYP) ELSE (0) END ))) AND (((( CASE WHEN (NOT (Prod_t.DS_SALES_HIER_AMT.DISTRIBUTION_AGREEMENT_SK IS
NULL )) THEN (Prod_t.DS_SALES_HIER_AMT.DISTRIBUTION_AGREEMENT_SK) ELSE (0) END ))= (( CASE WHEN (NOT
(prod_enh_t.ENH_SALES_HIER_AMT.DISTRIBUTION_AGREEMENT_SK IS NULL )) THEN (prod_enh_t.ENH_SALES_HIER_AMT.DISTRIBUTION_AGREEMENT_SK) ELSE (0) END
))) AND (((( CASE WHEN (NOT (Prod_t.DS_SALES_HIER_AMT.APPOINTED_PRODUCER_SK IS NULL )) THEN (Prod_t.DS_SALES_HIER_AMT.APPOINTED_PRODUCER_SK) ELSE
(0) END ))= (( CASE WHEN (NOT (prod_enh_t.ENH_SALES_HIER_AMT.APPOINTED_PRODUCER_SK IS NULL)) THEN
(prod_enh_t.ENH_SALES_HIER_AMT.APPOINTED_PRODUCER_SK) ELSE (0) END ))) AND ((Prod_t.DS_SALES_HIER_AMT.SALES_HIER_UID =
prod_enh_t.ENH_SALES_HIER_AMT.SALES_HIER_UID) AND ((Prod_t.DS_SALES_HIER_AMT.ACCT_DT = prod_enh_t.ENH_SALES_HIER_AMT.ACCT_DT )AND
((Prod_t.DS_SALES_HIER_AMT.COV_COMP_SK = prod_enh_t.ENH_SALES_HIER_AMT.COV_COMP_SK) AND ((Prod_t.DS_SALES_HIER_AMT.WRIT_APPT_PRDCR_SK =
prod_enh_t.ENH_SALES_HIER_AMT.WRIT_APPT_PRDCR_SK) AND (Prod_t.DS_SALES_HIER_AMT.POLICY_SK = prod_enh_t.ENH_SALES_HIER_AMT.POLICY_SK ))))))))").
The result goes into Spool 7 (all_amps), which is built locally on the AMPs. The size of Spool 7 is estimated with no confidence to be 3,939,932
rows (681,608,236 bytes). The estimated time for this step is 0.57 seconds.
| | |
| | 6) | We do an all-AMPs RETRIEVE step from Spool 7 by way of an all-rows scan with a condition of ("(( CASE WHEN (NOT (ENH_CORCT_CD IS NULL ))
THEN (ENH_CORCT_CD) ELSE ('A') END ))= 'A'") into Spool 1 (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with
no confidence to be 3,939,932 rows ( 1,761,149,604 bytes). The estimated time for this step is 1.84 seconds.
| |
| | 7) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from prod_enh_t.ENH_SALES_HIER_AMT by way of an all-rows scan with a condition of ("(NOT
(prod_enh_t.ENH_SALES_HIER_AMT.SALES_HIER_UID IS NULL )) AND ((prod_enh_t.ENH_SALES_HIER_AMT.ENH_REC_STAT = 1) AND
(prod_enh_t.ENH_SALES_HIER_AMT.ENH_CORCT_CD = 'I'))") into Spool 1 (all_amps), which is built locally on the AMPs. The size of Spool 1 is
estimated with no confidence to be 3,940,142 rows (1,761,243,474 bytes). The estimated time for this step is 0.03 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from Spool 7 (Last Use) by way of an all-rows scan with a condition of ("(( CASE WHEN (NOT (ENH_CORCT_CD
IS NULL )) THEN (ENH_CORCT_CD) ELSE ('A') END ))= 'A'") into Spool 2 (all_amps), which is built locally on the AMPs. The size of Spool 2 is
estimated with no confidence to be 3,939,932 rows (1,761,149,604 bytes). The estimated time for this step is 1.84 seconds.
| | |
| | 8) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from prod_enh_t.ENH_SALES_HIER_AMT by way of an all-rows scan with a condition of
("(prod_enh_t.ENH_SALES_HIER_AMT.ENH_REC_STAT = 1) AND (prod_enh_t.ENH_SALES_HIER_AMT.ENH_CORCT_CD = 'I')") into Spool 2 (all_amps), which is
built locally on the AMPs. The size of Spool 2 is estimated with no confidence to be 3,940,142 rows (1,761,243,474 bytes). The estimated time for
this step is 0.03 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from PROD_T.DS_DISTRIBUTION_AGREEMENT by way of an all-rows scan with a condition of (
"PROD_T.DS_DISTRIBUTION_AGREEMENT.Current_Rec_Ind = 1") into Spool 19 (all_amps), which is duplicated on all AMPs. Then we do a SORT to order
Spool 19 by the hash code of ( PROD_T.DS_DISTRIBUTION_AGREEMENT.Distribution_Role_Type,
PROD_T.DS_DISTRIBUTION_AGREEMENT.Distribution_Agreement_SK). The size of Spool 19 is estimated with low confidence to be 477,504 rows (11,937,600
bytes). The estimated time for this step is 0.03 seconds.
| | |
| | 9) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of an all-rows scan with a condition of ("NOT (SAT.POLICY_SK IS NULL)")
into Spool 20 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 20 by the hash code of (DISTRIBUTION_AGREEMENT_SK,
DSTRB_ROLE_TYP). The size of Spool 20 is estimated with no confidence to be 3,940,142 rows (240,348,662 bytes). The estimated time for this step
is 0.75 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from PROD_T.DS_APPOINTED_PRODUCER by way of an all-rows scan with a condition of (
"PROD_T.DS_APPOINTED_PRODUCER.Current_Rec_Ind = 1") into Spool 21 (all_amps), which is redistributed by the hash code of
(PROD_T.DS_APPOINTED_PRODUCER.Producer_Role_Player_SK) to all AMPs. Then we do a SORT to order Spool 21 by row hash. The size of Spool 21 is
estimated with low confidence to be 499,800 rows (14,494,200 bytes). The estimated time for this step is 0.35 seconds.
| | |
| | 10) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 19 (Last Use) by way of a RowHash match scan, which is joined to Spool 20 (Last Use) by way of a
RowHash match scan. Spool 19 and Spool 20 are right outer joined using a merge join, with condition(s) used for non-matching on right table
("(NOT (DSTRB_ROLE_TYP IS NULL)) AND (NOT (DISTRIBUTION_AGREEMENT_SK IS NULL ))"), with a join condition of ("(DSTRB_ROLE_TYP =
Distribution_Role_Type) AND (DISTRIBUTION_AGREEMENT_SK = Distribution_Agreement_SK)"). The result goes into Spool 22 (all_amps), which is
redistributed by the hash code of (POLICY_SK) to all AMPs. Then we do a SORT to order Spool 22 by row hash. The size of Spool 22 is estimated
with no confidence to be 3,940,142 rows (193,066,958 bytes). The estimated time for this step is 1.04 seconds.
| | |
| | |
| | 2) | We do an all-AMPs JOIN step from PROD_T.DS_ROLE_PLAYER by way of a RowHash match scan with a condition of
("PROD_T.DS_ROLE_PLAYER.Current_Rec_Ind = 1"), which is joined to Spool 21 by way of a RowHash match scan. PROD_T.DS_ROLE_PLAYER and Spool 21 are
right outer joined using a merge join, with a join condition of ("Producer_Role_Player_SK = PROD_T.DS_ROLE_PLAYER.Role_Player_SK"). The result
goes into Spool 25 (all_amps), which is redistributed by the hash code of (PROD_T.DS_APPOINTED_PRODUCER.Appointed_Producer_SK) to all AMPs. Then
we do a SORT to order Spool 25 by row hash. The size of Spool 25 is estimated with low confidence to be 632,372 rows (35,412,832 bytes). The
estimated time for this step is 1.54 seconds.
| | |
| | 11) | We do an all-AMPs JOIN step from PROD_T.DS_COVERAGE_COMPONENT by way of a RowHash match scan with a condition of
("PROD_T.DS_COVERAGE_COMPONENT.Current_Rec_Ind = 1"), which is joined to Spool 22 (Last Use) by way of a RowHash match scan.
PROD_T.DS_COVERAGE_COMPONENT and Spool 22 are right outer joined using a merge join, with condition(s) used for non-matching on right table ("(NOT
(COV_COMP_SK IS NULL )) AND (NOT (POLICY_SK IS NULL ))"), with a join condition of ("(COV_COMP_SK = PROD_T.DS_COVERAGE_COMPONENT.Cov_Comp_SK) AND
(POLICY_SK = PROD_T.DS_COVERAGE_COMPONENT.Policy_SK)"). The result goes into Spool 28 (all_amps), which is redistributed by the hash code of
(APPOINTED_PRODUCER_SK) to all AMPs. Then we do a SORT to order Spool 28 by row hash. The size of Spool 28 is estimated with no confidence to be
3,943,761 rows (161,694,201 bytes). The estimated time for this step is 11.20 seconds.
| |
| | 12) | We do an all-AMPs JOIN step from Spool 25 by way of a RowHash match scan, which is joined to Spool 28 (Last Use) by way of a RowHash match
scan. Spool 25 and Spool 28 are right outer joined using a merge join, with condition(s) used for non-matching on right table ("NOT
(APPOINTED_PRODUCER_SK IS NULL)"), with a join condition of ("APPOINTED_PRODUCER_SK = Appointed_Producer_SK"). The result goes into Spool 31
(all_amps), which is redistributed by the hash code of (POLICY_SK) to all AMPs. Then we do a SORT to order Spool 31 by row hash. The size of
Spool 31 is estimated with no confidence to be 5,018,795 rows (165,620,235 bytes). The estimated time for this step is 0.93 seconds.
| |
| | 13) | We do an all-AMPs JOIN step from PROD_T.DS_POLICY by way of a RowHash match scan with a condition of ("PROD_T.DS_POLICY.Current_Rec_Ind =
1"), which is joined to Spool 31 (Last Use) by way of a RowHash match scan. PROD_T.DS_POLICY and Spool 31 are joined using a merge join, with a
join condition of ("PROD_T.DS_POLICY.Policy_SK = POLICY_SK"). The result goes into Spool 18 (all_amps), which is built locally on the AMPs. The
size of Spool 18 is estimated with no confidence to be 25,613,894 rows (691,575,138 bytes). The estimated time for this step is 13.85 seconds.
| |
| | 14) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs SUM step to aggregate from Spool 18 (Last Use) by way of an all-rows scan , grouping by field1 (SALES_HIER_UID).
Aggregate Intermediate Results are computed globally, then placed in Spool 34. The size of Spool 34 is estimated with no confidence to be 373,931
rows (12,339,723 bytes). The estimated time for this step is 4.57 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan with a condition of
("NOT(DS_ENH_VW1_SALES_HIER_AMT.SALES_HIER_UID IS NULL)") into Spool 36 (all_amps), which is redistributed by the hash code of (PRDCR_LVL_NUM,
SALES_HIER_UID) to all AMPs. Then we do a SORT to order Spool 36 by row hash. The size of Spool 36 is estimated with no confidence to be
3,940,142 rows (271,869,798 bytes). The estimated time for this step is 1.29 seconds.
| | |
| | 15) | We do an all-AMPs RETRIEVE step from Spool 34 (Last Use) by way of an all-rows scan into Spool 37 (all_amps), which is redistributed by
the hash code of (SALES_HIER_UID, PRDCR_LVL_NUM) to all AMPs. Then we do a SORT to order Spool 37 by row hash. The size of Spool 37 is estimated
with no confidence to be 373,931 rows (9,348,275 bytes). The estimated time for this step is 0.06 seconds.
| |
| | 16) | We do an all-AMPs JOIN step from Spool 36 (Last Use) by way of an all-rows scan, which is joined to Spool 37 (Last Use) by way of an all-
rows scan. Spool 36 and Spool 37 are joined using an inclusion merge join, with a join condition of ("(SALES_HIER_UID = SALES_HIER_UID) AND
(PRDCR_LVL_NUM = Field_3)"). The result goes into Spool 12 (all_amps), which is built locally on the AMPs. The size of Spool 12 is estimated
with no confidence to be 3,940,142 rows (263,989,514 bytes). The estimated time for this step is 0.53 seconds.
| |
| | 17) | We do an all-AMPs SUM step to aggregate from Spool 12 (Last Use) by way of an all-rows scan , grouping by field1 ( SALES_HIER_UID,
POLICY_SK, COV_COMP_SK, APPOINTED_PRODUCER_SK ,ACCT_DT ,SALES_AMT,( CASE WHEN (SHR_PCT > 1.00000000000000E 000) THEN (SHR_PCT / 100) ELSE
(SHR_PCT) END)). Aggregate Intermediate Results are computed globally, then placed in Spool 38. The size of Spool 38 is estimated with no
confidence to be 3,475,299 rows (420,511,179 bytes). The estimated time for this step is 3.69 seconds.
| |
| | 18) | We do an all-AMPs STAT FUNCTION step from Spool 38 (Last Use) by way of an all-rows scan into Spool 42 (Last Use), which is redistributed
by hash code to all AMPs. The result rows are put into Spool 40 (all_amps), which is built locally on the AMPs. The size is estimated with no
confidence to be 3,475,299 rows (309,301,611 bytes).
| |
| | 19) | We do an all-AMPs STAT FUNCTION step from Spool 40 (Last Use) by way of an all-rows scan into Spool 45 (Last Use), which is redistributed
by hash code to all AMPs. The result rows are put into Spool 44 (all_amps), which is built locally on the AMPs. The size is estimated with no
confidence to be 3,475,299 rows (392,708,787 bytes).
| |
| | 20) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from Spool 44 (Last Use) by way of an all-rows scan into Spool 3 (used to materialize view, derived table
or table function SHA) (all_amps), which is built locally on the AMPs. The size of Spool 3 is estimated with no confidence to be 3,475,299 rows
(309,301,611 bytes). The estimated time for this step is 0.55 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER by way of an all-rows scan with
a condition of ("(PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER.Distribution_Role_Type = 1500014) AND
(PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER.Current_Rec_Ind = 1)") into Spool 51 (all_amps), which is duplicated on all
AMPs. The size of Spool 51 is estimated with low confidence to be 2,016 rows (34,272 bytes). The estimated time for this step is 0.03 seconds.
| | |
| | 21) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an all-rows scan with a condition of ("(NOT(SH.WRIT_APPT_PRDCR_SK IS NULL
)) AND ((NOT (SH.SALES_HIER_UID IS NULL )) AND (NOT (SH.POLICY_SK IS NULL )))"), which is joined to Spool 51 (Last Use) by way of an all-rows
scan. Spool 4 and Spool 51 are left outer joined using a product join, with condition(s) used for non-matching on left table ("NOT
(CHANNEL_DSTRB_AGRE_SK IS NULL)"), with a join condition of ("CHANNEL_DSTRB_AGRE_SK = Distribution_Agreement_SK"). The result goes into Spool 52
(all_amps), which is built locally on the AMPs. The size of Spool 52 is estimated with low confidence to be 2,979,681 rows (658,509,501 bytes).
The estimated time for this step is 1.74 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER by way of an all-rows scan with
a condition of ("(PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER.Distribution_Role_Type = 1500010) AND
(PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER.Current_Rec_Ind = 1)") into Spool 53 (all_amps), which is duplicated on all
AMPs. The size of Spool 53 is estimated with low confidence to be 11,616 rows (197,472 bytes). The estimated time for this step is 0.03 seconds.
| | |
| | |
| | 3) | We do an all-AMPs RETRIEVE step from PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER by way of an all-rows scan with
a condition of ("(PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER.Distribution_Role_Type = 1500011) AND
(PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER.Current_Rec_Ind = 1)") into Spool 54 (all_amps), which is duplicated on all
AMPs. Then we do a SORT to order Spool 54 by the hash code of (PROD_T.DS_DISTRIBUTION_AGREEMENT.Distribution_Agreement_SK). The size of Spool 54
is estimated with low confidence to be 144,960 rows (2,464,320 bytes). The estimated time for this step is 0.02 seconds.
| | |
| | 22) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 52 (Last Use) by way of an all-rows scan, which is joined to Spool 53 (Last Use) by way of an all-
rows scan. Spool 52 and Spool 53 are left outer joined using a product join, with condition(s) used for non-matching on left table ("NOT
(RVP_DSTRB_AGRE_SK IS NULL)"), with a join condition of ("RVP_DSTRB_AGRE_SK =Distribution_Agreement_SK"). The result goes into Spool 55
(all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 55 by the hash code of (KEY_REL_DSTRB_AGRE_SK). The size of
Spool 55 is estimated with low confidence to be 2,979,681 rows (634,672,053 bytes). The estimated time for this step is 3.14 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER by way of an all-rows scan with
a condition of ("(PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER.Distribution_Role_Type = 1500012) AND
(PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER.Current_Rec_Ind = 1)") into Spool 56 (all_amps), which is duplicated on all
AMPs. Then we do a SORT to order Spool 56 by the hash code of (PROD_T.DS_DISTRIBUTION_AGREEMENT.Distribution_Agreement_SK). The size of Spool 56
is estimated with low confidence to be 291,264 rows (4,951,488 bytes). The estimated time for this step is 0.02 seconds.
| | |
| | 23) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 54 (Last Use) by way of a RowHash match scan, which is joined to Spool 55 (Last Use) by way of a
RowHash match scan. Spool 54 and Spool 55 are right outer joined using a merge join, with condition(s) used for non-matching on right table ("NOT
(KEY_REL_DSTRB_AGRE_SK IS NULL)"), with a join condition of ("KEY_REL_DSTRB_AGRE_SK = Distribution_Agreement_SK"). The result goes into Spool 57
(all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 57 by the hash code of (AFIL_DSTRB_AGRE_SK). The size of Spool
57 is estimated with low confidence to be 2,979,681 rows (610,834,605 bytes). The estimated time for this step is 2.19 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER by way of an all-rows scan with
a condition of ("(PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER.Distribution_Role_Type = 1500013) AND
(PROD_T.DS_DISTRIBUTION_AGREEMENT in view PROD_V.DS_ENH_VW_SALES_HIER.Current_Rec_Ind = 1)") into Spool 66 (all_amps), which is duplicated on all
AMPs. Then we do a SORT to order Spool 66 by the hash code of (PROD_T.DS_DISTRIBUTION_AGREEMENT.Distribution_Agreement_SK). The size of Spool 66
is estimated with low confidence to be 27,648 rows (470,016 bytes). The estimated time for this step is 0.02 seconds.
| | |
| | 24) | We do an all-AMPs JOIN step from Spool 56 (Last Use) by way of a RowHash match scan, which is joined to Spool 57 (Last Use) by way of a
RowHash match scan. Spool 56 and Spool 57 are right outer joined using a merge join, with condition(s) used for non-matching on right table ("NOT
(AFIL_DSTRB_AGRE_SK IS NULL)"), with a join condition of ("AFIL_DSTRB_AGRE_SK = Distribution_Agreement_SK"). The result goes into Spool 67
(all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 67 by the hash code of (WHLSLR_TYP_AGRE_SK). The size of Spool
67 is estimated with low confidence to be 2,979,681 rows (586,997,157 bytes). The estimated time for this step is 2.06 seconds.
| |
| | 25) | We do an all-AMPs JOIN step from Spool 66 (Last Use) by way of a RowHash match scan, which is joined to Spool 67 (Last Use) by way of a
RowHash match scan. Spool 66 and Spool 67 are right outer joined using a merge join, with condition(s) used for non-matching on right table ("NOT
(WHLSLR_TYP_AGRE_SK IS NULL)"), with a join condition of ("WHLSLR_TYP_AGRE_SK = Distribution_Agreement_SK"). The result goes into Spool 77
(all_amps), which is redistributed by the hash code of (PARNT_PRDCR_LVL_6_SK) to all AMPs. Then we do a SORT to order Spool 77 by row hash. The
size of Spool 77 is estimated with low confidence to be 2,979,681 rows (563,159,709 bytes). The estimated time for this step is 2.94 seconds.
| |
| | 26) | We do an all-AMPs JOIN step from Spool 25 by way of a RowHash match scan, which is joined to Spool 77 (Last Use) by way of a RowHash match
scan. Spool 25 and Spool 77 are right outer joined using a merge join, with condition(s) used for non-matching on right table ("NOT
(PARNT_PRDCR_LVL_6_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_6_SK = Appointed_Producer_SK"). The result goes into Spool 83
(all_amps), which is redistributed by the hash code of (PARNT_PRDCR_LVL_7_SK) to all AMPs. Then we do a SORT to order Spool 83 by row hash. The
size of Spool 83 is estimated with low confidence to be 3,047,475 rows (551,592,975 bytes). The estimated time for this step is 2.96 seconds.
| |
| | 27) | We do an all-AMPs JOIN step from Spool 25 by way of a RowHash match scan, which is joined to Spool 83 (Last Use) by way of a RowHash match
scan. Spool 25 and Spool 83 are right outer joined using a merge join, with condition(s) used for non-matching on right table ("NOT
(PARNT_PRDCR_LVL_7_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_7_SK = Appointed_Producer_SK"). The result goes into Spool 86
(all_amps), which is redistributed by the hash code of (PARNT_PRDCR_LVL_10_SK) to all AMPs. Then we do a SORT to order Spool 86 by row hash. The
size of Spool 86 is estimated with low confidence to be 3,067,405 rows (530,661,065 bytes). The estimated time for this step is 2.93 seconds.
| |
| | 28) | We do an all-AMPs JOIN step from Spool 86 (Last Use) by way of a RowHash match scan, which is joined to Spool 25 by way of a RowHash match
scan. Spool 86 and Spool 25 are left outer joined using a merge join, with condition(s) used for non-matching on left table ("NOT
(PARNT_PRDCR_LVL_10_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_10_SK = Appointed_Producer_SK"). The result goes into Spool 95
(all_amps), which is redistributed by the hash code of (PARNT_PRDCR_LVL_5_SK) to all AMPs. Then we do a SORT to order Spool 95 by row hash. The
size of Spool 95 is estimated with low confidence to be 3,067,895 rows (506,202,675 bytes). The estimated time for this step is 2.59 seconds.
| |
| | 29) | We do an all-AMPs JOIN step from Spool 25 by way of a RowHash match scan, which is joined to Spool 95 (Last Use) by way of a RowHash match
scan. Spool 25 and Spool 95 are right outer joined using a merge join, with condition(s) used for non-matching on right table ("NOT
(PARNT_PRDCR_LVL_5_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_5_SK = Appointed_Producer_SK"). The result goes into Spool 99
(all_amps), which is redistributed by the hash code of (PARNT_PRDCR_LVL_8_SK) to all AMPs. Then we do a SORT to order Spool 99 by row hash. The
size of Spool 99 is estimated with low confidence to be 3,322,209 rows (521,586,813 bytes). The estimated time for this step is 3.00 seconds.
| |
| | 30) | We do an all-AMPs JOIN step from Spool 25 by way of a RowHash match scan, which is joined to Spool 99 (Last Use) by way of a RowHash match
scan. Spool 25 and Spool 99 are right outer joined using a merge join, with condition(s) used for non-matching on right table ("NOT
(PARNT_PRDCR_LVL_8_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_8_SK = Appointed_Producer_SK"). The result goes into Spool 106
(all_amps), which is redistributed by the hash code of (PARNT_PRDCR_LVL_4_SK) to all AMPs. Then we do a SORT to order Spool 106 by row hash. The
size of Spool 106 is estimated with low confidence to be 3,330,664 rows (496,268,936 bytes). The estimated time for this step is 2.52 seconds.
| |
| | 31) | We do an all-AMPs JOIN step from Spool 25 by way of a RowHash match scan, which is joined to Spool 106 (Last Use) by way of a RowHash
match scan. Spool 25 and Spool 106 are right outer joined using a merge join, with condition(s) used for non-matching on right table ("NOT
(PARNT_PRDCR_LVL_4_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_4_SK = Appointed_Producer_SK"). The result goes into Spool 112
(all_amps), which is redistributed by the hash code of (WRIT_APPT_PRDCR_SK) to all AMPs. Then we do a SORT to order Spool 112 by row hash. The
size of Spool 112 is estimated with low confidence to be 4,160,897 rows (586,686,477 bytes). The estimated time for this step is 3.01 seconds.
| |
| | 32) | We do an all-AMPs JOIN step from Spool 25 by way of a RowHash match scan, which is joined to Spool 112 (Last Use) by way of a RowHash
match scan. Spool 25 and Spool 112 are right outer joined using a merge join, with a join condition of ("WRIT_APPT_PRDCR_SK =
Appointed_Producer_SK"). The result goes into Spool 123 (all_amps), which is redistributed by the hash code of (PARNT_PRDCR_LVL_9_SK) to all
AMPs. Then we do a SORT to order Spool 123 by row hash. The size of Spool 123 is estimated with low confidence to be 6,818,950 rows
(1,200,135,200 bytes). The estimated time for this step is 8.56 seconds.
| |
| | 33) | We do an all-AMPs JOIN step from Spool 123 (Last Use) by way of a RowHash match scan, which is joined to Spool 25 by way of a RowHash
match scan. Spool 123 and Spool 25 are left outer joined using a merge join, with condition(s) used for non-matching on left table ("NOT
(PARNT_PRDCR_LVL_9_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_9_SK = Appointed_Producer_SK"). The result goes into Spool 132
(all_amps), which is redistributed by the hash code of (PARNT_PRDCR_LVL_3_SK) to all AMPs. Then we do a SORT to order Spool 132 by row hash. The
size of Spool 132 is estimated with low confidence to be 6,823,948 rows (1,146,423,264 bytes). The estimated time for this step is 6.66 seconds.
| |
| | 34) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 25 by way of a RowHash match scan, which is joined to Spool 132 (Last Use) by way of a RowHash
match scan. Spool 25 and Spool 132 are right outer joined using a merge join, with condition(s) used for non-matching on right table ("NOT
(PARNT_PRDCR_LVL_3_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_3_SK = Appointed_Producer_SK"). The result goes into Spool 133
(all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 133 by the hash code of (PARNT_PRDCR_LVL_13_SK). The size of
Spool 133 is estimated with low confidence to be 10,639,049 rows (1,702,247,840 bytes). The estimated time for this step is 6.90 seconds.
| | |
| | |
| | 2) | We do an all-AMPs JOIN step from PROD_T.DS_ROLE_PLAYER in view PROD_V.DS_ENH_VW_SALES_HIER by way of a RowHash match scan with a
condition of ("PROD_T.DS_ROLE_PLAYER in view PROD_V.DS_ENH_VW_SALES_HIER.Current_Rec_Ind = 1"), which is joined to Spool 21 (Last Use) by way of a
RowHash match scan. PROD_T.DS_ROLE_PLAYER and Spool 21 are right outer joined using a merge join, with a join condition of
("Producer_Role_Player_SK = PROD_T.DS_ROLE_PLAYER.Role_Player_SK"). The result goes into Spool 136 (all_amps), which is duplicated on all AMPs.
Then we do a SORT to order Spool 136 by the hash code of (PROD_T.DS_APPOINTED_PRODUCER.Appointed_Producer_SK). The size of Spool 136 is estimated
with low confidence to be 60,707,712 rows (1,274,861,952 bytes). The estimated time for this step is 7.32 seconds.
| | |
| | 35) | We do an all-AMPs JOIN step from Spool 133 (Last Use) by way of a RowHash match scan, which is joined to Spool 136 by way of a RowHash
match scan. Spool 133 and Spool 136 are left outer joined using a merge join, with condition(s) used for non-matching on left table ("NOT
(PARNT_PRDCR_LVL_13_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_13_SK = Appointed_Producer_SK"). The result goes into Spool 139
(all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 139 by the hash code of (PARNT_PRDCR_LVL_14_SK). The size of
Spool 139 is estimated with low confidence to be 10,639,050 rows (1,617,135,600 bytes). The estimated time for this step is 6.97 seconds.
| |
| | 36) | We do an all-AMPs JOIN step from Spool 139 (Last Use) by way of a RowHash match scan, which is joined to Spool 136 by way of a RowHash
match scan. Spool 139 and Spool 136 are left outer joined using a merge join, with condition(s) used for non-matching on left table ("NOT
(PARNT_PRDCR_LVL_14_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_14_SK = Appointed_Producer_SK"). The result goes into Spool 143
(all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 143 by the hash code of (PARNT_PRDCR_LVL_15_SK). The size of
Spool 143 is estimated with low confidence to be 10,639,051 rows (1,532,023,344 bytes). The estimated time for this step is 6.58 seconds.
| |
| | 37) | We do an all-AMPs JOIN step from Spool 143 (Last Use) by way of a RowHash match scan, which is joined to Spool 136 by way of a RowHash
match scan. Spool 143 and Spool 136 are left outer joined using a merge join, with condition(s) used for non-matching on left table ("NOT
(PARNT_PRDCR_LVL_15_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_15_SK = Appointed_Producer_SK"). The result goes into Spool 150
(all_amps), which is redistributed by the hash code of (PARNT_PRDCR_LVL_2_SK) to all AMPs. Then we do a SORT to order Spool 150 by row hash. The
size of Spool 150 is estimated with low confidence to be 10,639,052 rows ( 1,446,911,072 bytes). The estimated time for this step is 8.71
seconds.
| |
| | 38) | We do an all-AMPs JOIN step from Spool 25 (Last Use) by way of a RowHash match scan, which is joined to Spool 150 (Last Use) by way of a
RowHash match scan. Spool 25 and Spool 150 are right outer joined using a merge join, with condition(s) used for non-matching on right table
("NOT (PARNT_PRDCR_LVL_2_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_2_SK = Appointed_Producer_SK"). The result goes into Spool 151
(all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 151 by the hash code of (PARNT_PRDCR_LVL_16_SK). The size of
Spool 151 is estimated with low confidence to be 17,426,907 rows (2,230,644,096 bytes). The estimated time for this step is 9.30 seconds.
| |
| | 39) | We do an all-AMPs JOIN step from Spool 151 (Last Use) by way of a RowHash match scan, which is joined to Spool 136 by way of a RowHash
match scan. Spool 151 and Spool 136 are left outer joined using a merge join, with condition(s) used for non-matching on left table ("NOT
(PARNT_PRDCR_LVL_16_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_16_SK = Appointed_Producer_SK"). The result goes into Spool 157
(all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 157 by the hash code of (PARNT_PRDCR_LVL_17_SK). The size of
Spool 157 is estimated with low confidence to be 17,426,908 rows (2,091,228,960 bytes). The estimated time for this step is 9.35 seconds.
| |
| | 40) | We do an all-AMPs JOIN step from Spool 157 (Last Use) by way of a RowHash match scan, which is joined to Spool 136 by way of a RowHash
match scan. Spool 157 and Spool 136 are left outer joined using a merge join, with condition(s) used for non-matching on left table ("NOT
(PARNT_PRDCR_LVL_17_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_17_SK = Appointed_Producer_SK"). The result goes into Spool 161
(all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 161 by the hash code of (PARNT_PRDCR_LVL_18_SK). The size of
Spool 161 is estimated with low confidence to be 17,426,909 rows (1,951,813,808 bytes). The estimated time for this step is 8.73 seconds.
| |
| | 41) | We do an all-AMPs JOIN step from Spool 161 (Last Use) by way of a RowHash match scan, which is joined to Spool 136 by way of a RowHash
match scan. Spool 161 and Spool 136 are left outer joined using a merge join, with condition(s) used for non-matching on left table ("NOT
(PARNT_PRDCR_LVL_18_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_18_SK = Appointed_Producer_SK"). The result goes into Spool 165
(all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 165 by the hash code of (PARNT_PRDCR_LVL_12_SK). The size of
Spool 165 is estimated with low confidence to be 17,426,910 rows (1,812,398,640 bytes). The estimated time for this step is 8.12 seconds.
| |
| | 42) | We do an all-AMPs JOIN step from Spool 165 (Last Use) by way of a RowHash match scan, which is joined to Spool 136 by way of a RowHash
match scan. Spool 165 and Spool 136 are left outer joined using a merge join, with condition(s) used for non-matching on left table ("NOT
(PARNT_PRDCR_LVL_12_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_12_SK = Appointed_Producer_SK"). The result goes into Spool 172
(all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 172 by the hash code of (PARNT_PRDCR_LVL_11_SK). The size of
Spool 172 is estimated with low confidence to be 17,426,916 rows (1,672,983,936 bytes). The estimated time for this step is 7.47 seconds.
| |
| | 43) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 136 by way of a RowHash match scan, which is joined to Spool 172 (Last Use) by way of a RowHash
match scan. Spool 136 and Spool 172 are right outer joined using a merge join, with condition(s) used for non-matching on right table ("NOT
(PARNT_PRDCR_LVL_11_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_11_SK = Appointed_Producer_SK"). The result goes into Spool 176
(all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 176 by the hash code of (PARNT_PRDCR_LVL_1_SK). The size of
Spool 176 is estimated with low confidence to be 17,427,297 rows (1,533,602,136 bytes). The estimated time for this step is 6.89 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan with a condition of ("(NOT(SHA.APPOINTED_PRODUCER_SK
IS NULL )) AND ((NOT(SHA.SALES_HIER_UID IS NULL )) AND ((SHA.ACCT_DT >= DATE '2011-01-02') AND (NOT (SHA.POLICY_SK IS NULL ))))") into Spool 179
(all_amps), which is redistributed by the hash code of (POLICY_SK) to all AMPs. Then we do a SORT to order Spool 179 by row hash. The size of
Spool 179 is estimated with no confidence to be 3,475,299 rows (253,696,827 bytes). The estimated time for this step is 1.21 seconds.
| | |
| | |
| | 3) | We do an all-AMPs RETRIEVE step from PROD_T.DS_POLICY in view PROD_V.DS_VW_POLICY by way of an all-rows scan with a condition of
("((PROD_T.DS_POLICY in view PROD_V.DS_VW_POLICY.Status_Cd >= 112) OR (PROD_T.DS_POLICY in view PROD_V.DS_VW_POLICY.Status_Cd <= 110 )) AND
((PROD_T.DS_POLICY in view PROD_V.DS_VW_POLICY.Current_Rec_Ind = 1) AND ((( CASE WHEN (PROD_T.DS_POLICY inview
PROD_V.DS_VW_POLICY.Carrier_Admin_System LIKE 'AS400%') THEN (PROD_T.DS_POLICY in view PROD_V.DS_VW_POLICY.Full_Policy_No) ELSE (PROD_T.DS_POLICY
in view PROD_V.DS_VW_POLICY.Policy_No) END ))= 'ab03257680'))") into Spool 180 (all_amps) fanned out into 2 hash join partitions, which is built
locally on the AMPs. The size of Spool 180 is estimated with no confidence to be 1,600,267 rows (96,016,020 bytes). The estimated time for this
step is 17.32 seconds.
| | |
| | 44) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 136 (Last Use) by way of a RowHash match scan, which is joined to Spool 176 (Last Use) by way of a
RowHash match scan. Spool 136 and Spool 176 are right outer joined using a merge join, with condition(s) used for non-matching on right table
("NOT(PARNT_PRDCR_LVL_1_SK IS NULL)"), with a join condition of ("PARNT_PRDCR_LVL_1_SK = Appointed_Producer_SK"). The result goes into Spool 181
(all_amps), which is redistributed by the hash code of (POLICY_SK) to all AMPs into 2 hash join partitions. The size of Spool 181 is estimated
with low confidence to be 28,560,153 rows (2,284,812,240 bytes). The estimated time for this step is 8.64 seconds.
| | |
| | |
| | 2) | We do an all-AMPs JOIN step from PROD_T.DS_POLICY by way of a RowHash match scan with a condition of ("PROD_T.DS_POLICY.Current_Rec_Ind =
1"), which is joined to Spool 179 (Last Use) by way of a RowHash match scan. PROD_T.DS_POLICY and Spool 179 are joined using a merge join, with a
join condition of ("POLICY_SK = PROD_T.DS_POLICY.Policy_SK"). The result goes into Spool 184 (all_amps), which is redistributed by the hash code
of (SALES_HIER_UID, APPOINTED_PRODUCER_SK) to all AMPs into 24 hash join partitions. The size of Spool 184 is estimated with no confidence to be
17,736,517 rows (1,170,610,122 bytes). The estimated time for this step is 16.65 seconds.
| | |
| | 45) | We do an all-AMPs JOIN step from Spool 180 (Last Use) by way of an all-rows scan, which is joined to Spool 181 (Last Use) by way of an
all-rows scan. Spool 180 and Spool 181 are joined using a hash join of 2 partitions, with a join condition of ("Policy_SK = POLICY_SK"). The
result goes into Spool 185 (all_amps), which is redistributed by the hash code of (WRIT_APPT_PRDCR_SK, SALES_HIER_UID) to all AMPs into 24 hash
join partitions. The size of Spool 185 is estimated with no confidence to be 22,916,871 rows (2,543,772,681 bytes). The estimated time for this
step is 9.26 seconds.
| |
| | 46) | We do an all-AMPs JOIN step from Spool 184 (Last Use) by way of an all-rows scan, which is joined to Spool 185 (Last Use) by way of an
all-rows scan. Spool 184 and Spool 185 are joined using a hash join of 24 partitions, with a join condition of ("(Current_Rec_Ind =
Current_Rec_Ind) AND ((WRIT_APPT_PRDCR_SK = APPOINTED_PRODUCER_SK) AND (SALES_HIER_UID = SALES_HIER_UID ))"). The result goes into Spool 50
(all_amps), which is built locally on the AMPs. The size of Spool 50 is estimated with no confidence to be 1,087,006,620 rows (152,180,926,800
bytes). The estimated time for this step is 3 minutes and 28 seconds.
| |
| | 47) | We do an all-AMPs SUM step to aggregate from Spool 50 (Last Use) by way of an all-rows scan , grouping by field1 ( (CASE WHEN
(PROD_T.DS_POLICY.Carrier_Admin_System LIKE 'AS400%') THEN (PROD_T.DS_POLICY.Full_Policy_No) ELSE (PROD_T.DS_POLICY.Policy_No) END) ,ACCT_DT
,PROD_T.DS_ROLE_PLAYER.Full_Nm ,SHR_PCT). Aggregate Intermediate Results are computed globally, then placed in Spool 186. The size of Spool 186
is estimated with no confidence to be 815,254,965 rows (182,617,112,160 bytes). The estimated time for this step is 19 minutes and 3 seconds.
| |
| | 48) | We do an all-AMPs RETRIEVE step from Spool 186 (Last Use) by way of an all-rows scan into Spool 48 (group_amps), which is built locally on
the AMPs. The size of Spool 48 is estimated with no confidence to be 815,254,965 rows (75,003,456,780 bytes). The estimated time for this step
is 2 minutes and 9 seconds.
| |
| | 49) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 48 are sent back to the user as the result of statement 1.
| |
| |
