All Forums Database
myheart46 4 posts Joined 11/08
18 Nov 2008
Excution plan change when increase field(have example)

when i increase the selected filed the estimate time will increase(in my oppinion i think the estimate time and plan will be the same although select '1' or select *)and when i increase filed to reach at one point , the excution plan is change (etc.from redistribute to dup to all amp)what happen and how to happen ?i have example script below ( i add column E.EVENT_OC_CODE and plan change)old script SELECT E.EVENT_ID, E.MAIN_EVENT_ID, E.MAIN_EVENT_IND, E.EVENT_START_DT, E.EDW_START_DT, DS.DATA_SOURCE, E.EVENT_CTL_ID, CASE WHEN COALESCE(E.ACCOUNT_NUM,'') <> '' THEN E.ACCOUNT_NUM WHEN COALESCE(E_MAIN.ACCOUNT_NUM,'') <> '' THEN E_MAIN.ACCOUNT_NUM ELSE NULL END AS ACCOUNT_NUM, CASE WHEN COALESCE(E.ACCOUNT_MODIFIER_NUM,'') <> '' THEN E.ACCOUNT_MODIFIER_NUM WHEN COALESCE(E_MAIN.ACCOUNT_MODIFIER_NUM,'') <> '' THEN E_MAIN.ACCOUNT_MODIFIER_NUM ELSE NULL END AS ACCOUNT_MODIFIER_NUM, CASE WHEN COALESCE(E.BASE_ACCOUNT_NUM,'') <> '' THEN E.BASE_ACCOUNT_NUM WHEN COALESCE(E_MAIN.BASE_ACCOUNT_NUM,'') <> '' THEN E_MAIN.BASE_ACCOUNT_NUM ELSE NULL END AS BASE_ACCOUNT_NUM, BDM.CPC_END_DT, E.EVENT_CURRENCY_CD, CASE WHEN COALESCE(E.GL_ACCOUNT_CODE_VAL,'') <> '' THEN E.GL_ACCOUNT_CODE_VAL ELSE '0' END AS GL_ACCOUNT_CODE_VAL, E.DEBIT_GL_ACCT_CODE, E.CREDIT_GL_ACCT_CODE, E.DEBIT_CREDIT_IND, CASE WHEN COALESCE(E.GL_PRODUCT_CODE_VAL,'') <> '' THEN E.GL_PRODUCT_CODE_VAL ELSE '0' END AS GL_PRODUCT_CODE_VAL /* E.EVENT_OC_CODE, E.EVENT_RC_CODE, E.EVENT_ACTIVITY_TYPE_CD, E.EVENT_GROUP_ID, E.LOCAL_AMT, E.EVENT_AMT */ FROM S1VTTEDW.EDW_EVENT AS E JOIN ( SELECT ADD_MONTHS(BD.BUSINESSDATE,-1) + 1 AS CPC_START_DT, BD.BUSINESSDATE AS CPC_END_DT FROM S1VTPCPC.VCPC_BUSINESSDATE_M AS BD ) AS BDM on E.EVENT_START_DT BETWEEN BDM.CPC_START_DT AND BDM.CPC_END_DT --Filter only focused source system INNER JOIN S1DPYCPC.CPC_MAP_DATA_SOURCE AS DS ON DS.SRC_CTL_ID = E.EVENT_CTL_ID and DS.DATA_SOURCE <> 'NO' --Retrieve Main Event to get Account_Num LEFT OUTER JOIN S1VTTEDW.EDW_EVENT AS E_MAIN ON E_MAIN.EVENT_ID = E.MAIN_EVENT_ID AND E_MAIN.EVENT_START_DT = E.EVENT_START_DT AND E_MAIN.MAIN_EVENT_IND = 'YES' AND COALESCE(E_MAIN.ACCOUNT_NUM,'') <> '' AND COALESCE(E_MAIN.ACCOUNT_MODIFIER_NUM,'') <> ''new script SELECT E.EVENT_ID, E.MAIN_EVENT_ID, E.MAIN_EVENT_IND, E.EVENT_START_DT, E.EDW_START_DT, DS.DATA_SOURCE, E.EVENT_CTL_ID, CASE WHEN COALESCE(E.ACCOUNT_NUM,'') <> '' THEN E.ACCOUNT_NUM WHEN COALESCE(E_MAIN.ACCOUNT_NUM,'') <> '' THEN E_MAIN.ACCOUNT_NUM ELSE NULL END AS ACCOUNT_NUM, CASE WHEN COALESCE(E.ACCOUNT_MODIFIER_NUM,'') <> '' THEN E.ACCOUNT_MODIFIER_NUM WHEN COALESCE(E_MAIN.ACCOUNT_MODIFIER_NUM,'') <> '' THEN E_MAIN.ACCOUNT_MODIFIER_NUM ELSE NULL END AS ACCOUNT_MODIFIER_NUM, CASE WHEN COALESCE(E.BASE_ACCOUNT_NUM,'') <> '' THEN E.BASE_ACCOUNT_NUM WHEN COALESCE(E_MAIN.BASE_ACCOUNT_NUM,'') <> '' THEN E_MAIN.BASE_ACCOUNT_NUM ELSE NULL END AS BASE_ACCOUNT_NUM, BDM.CPC_END_DT, E.EVENT_CURRENCY_CD, CASE WHEN COALESCE(E.GL_ACCOUNT_CODE_VAL,'') <> '' THEN E.GL_ACCOUNT_CODE_VAL ELSE '0' END AS GL_ACCOUNT_CODE_VAL, E.DEBIT_GL_ACCT_CODE, E.CREDIT_GL_ACCT_CODE, E.DEBIT_CREDIT_IND, CASE WHEN COALESCE(E.GL_PRODUCT_CODE_VAL,'') <> '' THEN E.GL_PRODUCT_CODE_VAL ELSE '0' END AS GL_PRODUCT_CODE_VAL, E.EVENT_OC_CODE /* E.EVENT_RC_CODE, E.EVENT_ACTIVITY_TYPE_CD, E.EVENT_GROUP_ID, E.LOCAL_AMT, E.EVENT_AMT */ FROM S1VTTEDW.EDW_EVENT AS E JOIN ( SELECT ADD_MONTHS(BD.BUSINESSDATE,-1) + 1 AS CPC_START_DT, BD.BUSINESSDATE AS CPC_END_DT FROM S1VTPCPC.VCPC_BUSINESSDATE_M AS BD ) AS BDM on E.EVENT_START_DT BETWEEN BDM.CPC_START_DT AND BDM.CPC_END_DT --Filter only focused source system INNER JOIN S1DPYCPC.CPC_MAP_DATA_SOURCE AS DS ON DS.SRC_CTL_ID = E.EVENT_CTL_ID and DS.DATA_SOURCE <> 'NO' --Retrieve Main Event to get Account_Num LEFT OUTER JOIN S1VTTEDW.EDW_EVENT AS E_MAIN ON E_MAIN.EVENT_ID = E.MAIN_EVENT_ID AND E_MAIN.EVENT_START_DT = E.EVENT_START_DT AND E_MAIN.MAIN_EVENT_IND = 'YES' AND COALESCE(E_MAIN.ACCOUNT_NUM,'') <> '' AND COALESCE(E_MAIN.ACCOUNT_MODIFIER_NUM,'') <> ''

myheart46 4 posts Joined 11/08
18 Nov 2008

old plan Explanation 1) First, we lock S1DPYCPC.DS for access, and we lock S1DTTEDW.EDW_EVENT for access. 2) Next, we do a single-AMP RETRIEVE step from S1DCFEDW.BDATE by way of the unique primary index "S1DCFEDW.BDATE.BusinessDateKey = 318" with no residual conditions locking row for access into Spool 2 (all_amps) (compressed columns allowed), which is duplicated on all AMPs. The size of Spool 2 is estimated with high confidence to be 45 rows. The estimated time for this step is 0.01 seconds. 3) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to S1DPYCPC.DS by way of an all-rows scan with a condition of ("(NOT (S1DPYCPC.DS.Src_Ctl_Id IS NULL )) AND (S1DPYCPC.DS.DATA_SOURCE <> 'NO')"). Spool 2 and S1DPYCPC.DS are joined using a product join, with a join condition of ("(1=1)"). The result goes into Spool 3 (all_amps) (compressed columns allowed), which is duplicated on all AMPs. The size of Spool 3 is estimated with high confidence to be 900 rows. The estimated time for this step is 0.02 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 an all-rows scan, which is joined to S1DTTEDW.EDW_EVENT by way of an all-rows scan. Spool 3 and S1DTTEDW.EDW_EVENT are joined using a product join, with a join condition of ( "(S1DTTEDW.EDW_EVENT.Event_Start_Dt <= BusinessDate) AND ((S1DTTEDW.EDW_EVENT.Event_Start_Dt >= ((ADD_MONTHS(({LeftTable}.BusinessDate ),-1 ))+ 1 )) AND (Src_Ctl_Id = S1DTTEDW.EDW_EVENT.Event_Ctl_Id ))"). The input table S1DTTEDW.EDW_EVENT will not be cached in memory, but it is eligible for synchronized scanning. The result goes into Spool 4 (all_amps) (compressed columns allowed), [color=4]which is redistributed by hash code to all AMPs. Then we do[/color] 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 low confidence to be 69,373,545 rows. The estimated time for this step is 32 minutes and 45 seconds. 2) We do an all-AMPs RETRIEVE step from S1DTTEDW.EDW_EVENT by way of an all-rows scan with a condition of ( "(S1DTTEDW.EDW_EVENT.Main_Event_Ind = 'YES') AND (((( CASE WHEN (NOT (S1DTTEDW.EDW_EVENT.Account_Num IS NULL )) THEN (S1DTTEDW.EDW_EVENT.Account_Num) ELSE ('') END ))<> '') AND ((( CASE WHEN (NOT (S1DTTEDW.EDW_EVENT.Account_Modifier_Num IS NULL )) THEN (S1DTTEDW.EDW_EVENT.Account_Modifier_Num) ELSE ('') END ))<> ''))") locking for access into Spool 5 (all_amps) (compressed columns allowed), 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 low confidence to be 16,036,388 rows. The estimated time for this step is 2 minutes and 17 seconds. 5) 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 left outer joined using a merge join, with a join condition of ( "(Event_Start_Dt = Event_Start_Dt) AND (Event_Id = Main_Event_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 low confidence to be 69,373,545 rows. The estimated time for this step is 2 minutes and 39 seconds. 6) 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 35 minutes and 25 seconds.

myheart46 4 posts Joined 11/08
18 Nov 2008

new plan Explanation 1) First, we lock S1DPYCPC.DS for access, and we lock S1DTTEDW.EDW_EVENT for access. 2) Next, we do a single-AMP RETRIEVE step from S1DCFEDW.BDATE by way of the unique primary index "S1DCFEDW.BDATE.BusinessDateKey = 318" with no residual conditions locking row for access into Spool 2 (all_amps) (compressed columns allowed), which is duplicated on all AMPs. The size of Spool 2 is estimated with high confidence to be 45 rows. The estimated time for this step is 0.01 seconds. 3) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to S1DPYCPC.DS by way of an all-rows scan with a condition of ("(NOT (S1DPYCPC.DS.Src_Ctl_Id IS NULL )) AND (S1DPYCPC.DS.DATA_SOURCE <> 'NO')"). Spool 2 and S1DPYCPC.DS are joined using a product join, with a join condition of ("(1=1)"). The result goes into Spool 3 (all_amps) (compressed columns allowed), which is duplicated on all AMPs. The size of Spool 3 is estimated with high confidence to be 900 rows. The estimated time for this step is 0.02 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 an all-rows scan, which is joined to S1DTTEDW.EDW_EVENT by way of an all-rows scan. Spool 3 and S1DTTEDW.EDW_EVENT are joined using a product join, with a join condition of ( "(S1DTTEDW.EDW_EVENT.Event_Start_Dt <= BusinessDate) AND ((S1DTTEDW.EDW_EVENT.Event_Start_Dt >= ((ADD_MONTHS(({LeftTable}.BusinessDate ),-1 ))+ 1 )) AND (Src_Ctl_Id = S1DTTEDW.EDW_EVENT.Event_Ctl_Id ))"). The input table S1DTTEDW.EDW_EVENT will not be cached in memory, but it is eligible for synchronized scanning. The result goes into Spool 4 (all_amps) (compressed columns allowed), which is built locally on the 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 low confidence to be 69,373,545 rows. The estimated time for this step is 5 minutes and 56 seconds. 2) We do an all-AMPs RETRIEVE step from S1DTTEDW.EDW_EVENT by way of an all-rows scan with a condition of ( "(S1DTTEDW.EDW_EVENT.Main_Event_Ind = 'YES') AND (((( CASE WHEN (NOT (S1DTTEDW.EDW_EVENT.Account_Num IS NULL )) THEN (S1DTTEDW.EDW_EVENT.Account_Num) ELSE ('') END ))<> '') AND ((( CASE WHEN (NOT (S1DTTEDW.EDW_EVENT.Account_Modifier_Num IS NULL )) THEN (S1DTTEDW.EDW_EVENT.Account_Modifier_Num) ELSE ('') END ))<> ''))") locking for access into Spool 5 [color=4] (all_amps) (compressed columns allowed), which is duplicated[/color] on 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 low confidence to be 721,637,460 rows. The estimated time for this step is 29 minutes and 56 seconds. 5) 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 left outer joined using a merge join, with a join condition of ( "(Event_Start_Dt = Event_Start_Dt) AND (Event_Id = Main_Event_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 low confidence to be 69,373,545 rows. The estimated time for this step is 3 minutes and 2 seconds. 6) 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 32 minutes and 57 seconds.

Adeel Chaudhry 773 posts Joined 04/08
18 Nov 2008

Hello,Just a small suggestion, it is very good for "all" to state problem clearly and comprehensively; and using attachment feature where necessary!Best Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

myheart46 4 posts Joined 11/08
18 Nov 2008

I try to identify my problem with clearlybut i cannot use font color to specific it T_Tplz someone help my problem.

You must sign in to leave a comment.