Excution plan change when increase field(have example)

Database

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,'') <> ''
4 REPLIES

Re: Excution plan change when increase field(have example)

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.

Re: Excution plan change when increase field(have example)

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.
Teradata Employee

Re: Excution plan change when increase field(have example)

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

Re: Excution plan change when increase field(have example)

I try to identify my problem with clearly

but i cannot use font color to specific it T_T

plz someone help my problem.