Hi All,
I need your input for performance improvement of below SQL (with EXPLAIN PLAN).
This SQL was running fine previously but suddenly having performance issue. We have refreshed the STATS but still problem persist.

explain SELECT
y.PlntIdCd,
y.PlngCtryCd,
y.DivCd,
y.AtOncePromoFutsCd,
y.ProdtCd,
y.GlblProdtCatCd,
y.GlblProdtSubcatCd,
y.SeasnYrRlvncDt,
y.PlngCRD_BusMoRlvncDt,
y.ShrtLeadTmInd,
y.MAD_MondayWkDt,
y.MAD_MondayWkSeqNbr,
y.RptgCnfrmdQty,
y.FutsFailATP_BkgsQty,
y.PerMoToWkCrvSO_Qty,
y.MstrDataPerMoToWkCrvRtio,
CASE
WHEN y.RatioRnk = 1 -- Identify the highest value
THEN 'Y'
ELSE 'N'
END AS MstrDataPeakInd,
y.OrigPerMoToWkCrvRtio,
y.AdjSO_PerMoToWkCrvRtio,
y.AdjPeakInd,
'Y' AS ActvInd,
-1 AS RowInsAdtFK,
-1 AS RowLastUpdtFK,
-1 AS OrigPhysSrcSysSK,
Current_Timestamp(0) AS SrcTmstmp,
'NOT USED' AS RcrdChkSum
FROM
(
SELECT
x.PlntIdCd,
x.PseudoPlngCtryCd AS PlngCtryCd,
x.DivCd,
x.GlblProdtCatCd,
x.GlblProdtSubcatCd,
x.AtOncePromoFutsCd,
x.SeasnYrRlvncDt,
x.PlngCRD_BusMoRlvncDt,
x.ProdtCd,
x.ShrtLeadTmInd,
x.MAD_MondayWkDt,
x.MAD_MondayWkSeqNbr,
x.RptgCnfrmdQty,
x.FutsFailATP_BkgsQty,
x.PerMoToWkCrvSO_Qty,
x.MstrDataPerMoToWkCrvRtio,
x.OrigPerMoToWkCrvRtio,
x.AdjSO_PerMoToWkCrvRtio,
x.AdjPeakInd,
RANK() OVER (PARTITION BY x.PlntIdCd, x.DivCd, x.AtOncePromoFutsCd, x.SeasnYrRlvncDt, x.PlngCRD_BusMoRlvncDt, x.ProdtCd, ShrtLeadTmInd ORDER BY x.MstrDataPerMoToWkCrvRtio DESC) AS RatioRnk
FROM
(
SELECT
so.PlntIdCd,
CASE
WHEN so.AtOncePromoFutsCd IN ('AT-ONCE', 'PROMO', 'FUTURES')
THEN so.PlntIdCd
ELSE so.PlngCtryCd
END AS PseudoPlngCtryCd,
so.DivCd,
so.GlblProdtCatCd,
so.GlblProdtSubcatCd,
so.AtOncePromoFutsCd,
so.SeasnYrRlvncDt,
so.PlngCRD_BusMoRlvncDt,
so.ProdtCd,
so.ShrtLeadTmInd,
so.MAD_MondayWkDt,
so.MAD_MondayWkSeqNbr,
so.RptgCnfrmdQty,
so.FutsFailATP_BkgsQty,
so.PerMoToWkCrvSO_Qty,
CAST((rule.PerDistribRtioNbr * 100) AS DECIMAL(38,6)) AS MstrDataPerMoToWkCrvRtio,
so.OrigPerMoToWkCrvRtio,
so.AdjSO_PerMoToWkCrvRtio,
so.AdjPeakInd,

CASE EXTRACT(MONTH FROM so.PlngCRD_BusMoRlvncDt) -- The master data table contains three letter month values. This is for joining.
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END AS PlngCRD_BusMoRlvncMo
FROM
PLN_ETL.NK_PerMoToWkCrvExcptnSO AS so -- This is the SO data that was prepared for this report.
INNER JOIN
PLN_ETL.AtOncePromoFuts AS aopf -- This is used to get the right code for joining to master data.
ON
so.AtOncePromoFutsCd = aopf.AtOncePromoFutsCd
LEFT OUTER JOIN
PLN_ETL.PerDistribPrflDefn_Norm_v AS rule -- This is the master data profile definition table.
ON
( so.DivCd = rule.DivCd
OR
rule.DivCd = '*')
AND
( so.PlntIdCd = rule.PlntIdCd
OR
rule.PlntIdCd = '*')
AND
( aopf.AtOncePromoFutsAltCd = rule.DmndStreamNm
OR
rule.DmndStreamNm = '*')
AND
( PlngCRD_BusMoRlvncMo = rule.CRD_BusMoOfYrShrtNm
OR
rule.CRD_BusMoOfYrShrtNm = '*')
AND
so.MAD_MondayWkSeqNbr = rule.PerOfstNbr
AND
rule.PerOfstNbr BETWEEN -5 AND 6 -- This identifies the range of weeks we're interested in.
QUALIFY ROW_NUMBER() OVER (PARTITION BY so.DivCd, PseudoPlngCtryCd, so.AtOncePromoFutsCd, so.ProdtCd, PlngCRD_BusMoRlvncMo, so.ShrtLeadTmInd, so.MAD_MondayWkDt ORDER BY rule.DmndStreamNm DESC, rule.CRD_BusMoOfYrShrtNm DESC, rule.DivCd DESC, rule.PlntIdCd DESC) = 1
) AS x
) AS y;

1) First, we lock PLN_T.NK_PerMoToWkCrvExcptnSO for access, we lock
PLN_T.PerDistribPrflDefn for access, and we lock
PLN_T.AtOncePromoFuts for access.
2) Next, we do an all-AMPs RETRIEVE step from
PLN_T.PerDistribPrflDefn by way of an all-rows scan with no
residual conditions into Spool 1 (all_amps), which is built
locally on the AMPs. The size of Spool 1 is estimated with high
confidence to be 2,144 rows (132,928 bytes). The estimated time
for this step is 0.02 seconds.
3) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by
way of an all-rows scan with no residual conditions locking for
access into Spool 1 (all_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 4,288
rows (265,856 bytes). The estimated time for this step is 0.02
seconds.
4) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by
way of an all-rows scan with no residual conditions locking for
access into Spool 1 (all_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 6,432
rows (398,784 bytes). The estimated time for this step is 0.02
seconds.
5) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by
way of an all-rows scan with no residual conditions locking for
access into Spool 1 (all_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 8,576
rows (531,712 bytes). The estimated time for this step is 0.02
seconds.
6) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by
way of an all-rows scan with no residual conditions locking for
access into Spool 1 (all_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 10,720
rows (664,640 bytes). The estimated time for this step is 0.02
seconds.
7) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by
way of an all-rows scan with no residual conditions locking for
access into Spool 1 (all_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 12,864
rows (797,568 bytes). The estimated time for this step is 0.02
seconds.
8) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by
way of an all-rows scan with no residual conditions locking for
access into Spool 1 (all_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 15,008
rows (930,496 bytes). The estimated time for this step is 0.02
seconds.
9) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by
way of an all-rows scan with no residual conditions locking for
access into Spool 1 (all_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 17,152
rows (1,063,424 bytes). The estimated time for this step is 0.02
seconds.
10) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by
way of an all-rows scan with no residual conditions locking for
access into Spool 1 (all_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 19,296
rows (1,196,352 bytes). The estimated time for this step is 0.02
seconds.
11) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by
way of an all-rows scan with no residual conditions locking for
access into Spool 1 (all_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 21,440
rows (1,329,280 bytes). The estimated time for this step is 0.02
seconds.
12) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by
way of an all-rows scan with no residual conditions locking for
access into Spool 1 (all_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 23,584
rows (1,462,208 bytes). The estimated time for this step is 0.02
seconds.
13) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn
by way of an all-rows scan with no residual conditions locking
for access into Spool 1 (all_amps), which is built locally on
the AMPs. The size of Spool 1 is estimated with high
confidence to be 25,728 rows (1,595,136 bytes). The estimated
time for this step is 0.02 seconds.
2) We do an all-AMPs RETRIEVE step from PLN_T.AtOncePromoFuts by
way of an all-rows scan with no residual conditions into Spool
5 (all_amps) (compressed columns allowed), which is duplicated
on all AMPs. The size of Spool 5 is estimated with high
confidence to be 1,440 rows (44,640 bytes). The estimated
time for this step is 0.02 seconds.
14) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of
an all-rows scan with a condition of ("(rule.PEROFSTNBR <= 6) AND
(rule.PEROFSTNBR >= -5)") into Spool 6 (all_amps) (compressed
columns allowed) fanned out into 50 hash join partitions, which is
duplicated on all AMPs. The size of Spool 6 is estimated with
high confidence to be 9,262,080 rows (574,248,960 bytes). The
estimated time for this step is 0.26 seconds.
15) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an
all-rows scan, which is joined to PLN_T.NK_PerMoToWkCrvExcptnSO by
way of an all-rows scan with no residual conditions. Spool 5 and
PLN_T.NK_PerMoToWkCrvExcptnSO are joined using a dynamic hash join,
with a join condition of (
"PLN_T.NK_PerMoToWkCrvExcptnSO.AtOncePromoFutsCd =
AtOncePromoFutsCd"). The result goes into Spool 7 (all_amps)
(compressed columns allowed) fanned out into 50 hash join
partitions, which is built locally on the AMPs. The size of Spool
7 is estimated with low confidence to be 46,605,984 rows (
8,342,471,136 bytes). The estimated time for this step is 3.42
seconds.
16) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an
all-rows scan, which is joined to Spool 7 (Last Use) by way of an
all-rows scan. Spool 6 and Spool 7 are right outer joined using a
hash join of 50 partitions, with condition(s) used for
non-matching on right table ("(MAD_MondayWkSeqNbr <= 6) AND
(MAD_MondayWkSeqNbr >= -5)"), with a join condition of (
"(MAD_MondayWkSeqNbr = PEROFSTNBR) AND ((((( CASE WHEN
((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 1) THEN ('Jan')
WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 2) THEN
('Feb') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 3)
THEN ('Mar') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))=
4) THEN ('Apr') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt
)))= 5) THEN ('May') WHEN ((EXTRACT(MONTH FROM
(PlngCRD_BusMoRlvncDt )))= 6) THEN ('Jun') WHEN ((EXTRACT(MONTH
FROM (PlngCRD_BusMoRlvncDt )))= 7) THEN ('Jul') WHEN
((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 8) THEN ('Aug')
WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 9) THEN
('Sep') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 10)
THEN ('Oct') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))=
11) THEN ('Nov') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt
)))= 12) THEN ('Dec') ELSE (NULL) END ))= CRD_BUSMOOFYRSHRTNM) OR
(CRD_BUSMOOFYRSHRTNM = '* ')) AND (((PlntIdCd =PLNTIDCD) OR
(PLNTIDCD = '* ')) AND (((DivCd = DIVCD) OR (DIVCD = '* ')) AND
((AtOncePromoFutsAltCd = DMNDSTREAMNM) OR (DMNDSTREAMNM = '*
')))))"). The result goes into Spool 4 (all_amps) (compressed
columns allowed), 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 99,923,229,696 rows (
22,482,726,681,600 bytes). The estimated time for this step is 1
hour and 51 minutes.
17) We do an all-AMPs STAT FUNCTION step from Spool 4 (Last Use) by
way of an all-rows scan into Spool 12 (Last Use), which is assumed
to be redistributed by value to all AMPs. The result rows are put
into Spool 10 (all_amps) (compressed columns allowed), which is
built locally on the AMPs. The size is estimated with low
confidence to be 99,923,229,696 rows (33,873,974,866,944 bytes).
18) We do an all-AMPs RETRIEVE step from Spool 10 (Last Use) by way of
an all-rows scan with a condition of ("Field_21 = 1") into Spool 2
(used to materialize view, derived table, table function or table
operator x) (all_amps) (compressed columns allowed), which is
built locally on the AMPs. The result spool file will not be
cached in memory. The size of Spool 2 is estimated with low
confidence to be 99,923,229,696 rows (19,884,722,709,504 bytes).
The estimated time for this step is 1 hour and 56 minutes.
19) We do an all-AMPs STAT FUNCTION step from Spool 2 (Last Use) by
way of an all-rows scan into Spool 18 (Last Use), which is
redistributed by hash code to all AMPs. The result rows are put
into Spool 16 (all_amps) (compressed columns allowed), which is
built locally on the AMPs. The size is estimated with low
confidence to be 99,923,229,696 rows (30,876,277,976,064 bytes).
The estimated time for this step is 2 hours and 3 minutes.
20) We do an all-AMPs RETRIEVE step from Spool 16 (Last Use) by way of
an all-rows scan into Spool 3 (used to materialize view, derived
table, table function or table operator y) (all_amps) (compressed
columns allowed), which is built locally on the AMPs. The result
spool file will not be cached in memory. The size of Spool 3 is
estimated with low confidence to be 99,923,229,696 rows (
20,284,415,628,288 bytes). The estimated time for this step is 2
hours and 6 minutes.
21) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
an all-rows scan into Spool 21 (group_amps), which is built
locally on the AMPs. The result spool file will not be cached in
memory. The size of Spool 21 is estimated with low confidence to
be 99,923,229,696 rows (30,576,508,286,976 bytes). The estimated
time for this step is 2 hours and 6 minutes.
22) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 21 are sent back to the user as the result
of statement 1.

Hi All,

I need your input for performance improvement of below SQL (with EXPLAIN PLAN).

This SQL was running fine previously but suddenly having performance issue. We have refreshed the STATS but still problem persist.

explain SELECT

y.PlntIdCd,

y.PlngCtryCd,

y.DivCd,

y.AtOncePromoFutsCd,

y.ProdtCd,

y.GlblProdtCatCd,

y.GlblProdtSubcatCd,

y.SeasnYrRlvncDt,

y.PlngCRD_BusMoRlvncDt,

y.ShrtLeadTmInd,

y.MAD_MondayWkDt,

y.MAD_MondayWkSeqNbr,

y.RptgCnfrmdQty,

y.FutsFailATP_BkgsQty,

y.PerMoToWkCrvSO_Qty,

y.MstrDataPerMoToWkCrvRtio,

CASE

WHEN y.RatioRnk = 1 -- Identify the highest value

THEN 'Y'

ELSE 'N'

END AS MstrDataPeakInd,

y.OrigPerMoToWkCrvRtio,

y.AdjSO_PerMoToWkCrvRtio,

y.AdjPeakInd,

'Y' AS ActvInd,

-1 AS RowInsAdtFK,

-1 AS RowLastUpdtFK,

-1 AS OrigPhysSrcSysSK,

Current_Timestamp(0) AS SrcTmstmp,

'NOT USED' AS RcrdChkSum

FROM

(

SELECT

x.PlntIdCd,

x.PseudoPlngCtryCd AS PlngCtryCd,

x.DivCd,

x.GlblProdtCatCd,

x.GlblProdtSubcatCd,

x.AtOncePromoFutsCd,

x.SeasnYrRlvncDt,

x.PlngCRD_BusMoRlvncDt,

x.ProdtCd,

x.ShrtLeadTmInd,

x.MAD_MondayWkDt,

x.MAD_MondayWkSeqNbr,

x.RptgCnfrmdQty,

x.FutsFailATP_BkgsQty,

x.PerMoToWkCrvSO_Qty,

x.MstrDataPerMoToWkCrvRtio,

x.OrigPerMoToWkCrvRtio,

x.AdjSO_PerMoToWkCrvRtio,

x.AdjPeakInd,

RANK() OVER (PARTITION BY x.PlntIdCd, x.DivCd, x.AtOncePromoFutsCd, x.SeasnYrRlvncDt, x.PlngCRD_BusMoRlvncDt, x.ProdtCd, ShrtLeadTmInd ORDER BY x.MstrDataPerMoToWkCrvRtio DESC) AS RatioRnk

FROM

(

SELECT

so.PlntIdCd,

CASE

WHEN so.AtOncePromoFutsCd IN ('AT-ONCE', 'PROMO', 'FUTURES')

THEN so.PlntIdCd

ELSE so.PlngCtryCd

END AS PseudoPlngCtryCd,

so.DivCd,

so.GlblProdtCatCd,

so.GlblProdtSubcatCd,

so.AtOncePromoFutsCd,

so.SeasnYrRlvncDt,

so.PlngCRD_BusMoRlvncDt,

so.ProdtCd,

so.ShrtLeadTmInd,

so.MAD_MondayWkDt,

so.MAD_MondayWkSeqNbr,

so.RptgCnfrmdQty,

so.FutsFailATP_BkgsQty,

so.PerMoToWkCrvSO_Qty,

CAST((rule.PerDistribRtioNbr * 100) AS DECIMAL(38,6)) AS MstrDataPerMoToWkCrvRtio,

so.OrigPerMoToWkCrvRtio,

so.AdjSO_PerMoToWkCrvRtio,

so.AdjPeakInd,

CASE EXTRACT(MONTH FROM so.PlngCRD_BusMoRlvncDt) -- The master data table contains three letter month values. This is for joining.

WHEN 1 THEN 'Jan'

WHEN 2 THEN 'Feb'

WHEN 3 THEN 'Mar'

WHEN 4 THEN 'Apr'

WHEN 5 THEN 'May'

WHEN 6 THEN 'Jun'

WHEN 7 THEN 'Jul'

WHEN 8 THEN 'Aug'

WHEN 9 THEN 'Sep'

WHEN 10 THEN 'Oct'

WHEN 11 THEN 'Nov'

WHEN 12 THEN 'Dec'

END AS PlngCRD_BusMoRlvncMo

FROM

PLN_ETL.NK_PerMoToWkCrvExcptnSO AS so -- This is the SO data that was prepared for this report.

INNER JOIN

PLN_ETL.AtOncePromoFuts AS aopf -- This is used to get the right code for joining to master data.

ON

so.AtOncePromoFutsCd = aopf.AtOncePromoFutsCd

LEFT OUTER JOIN

PLN_ETL.PerDistribPrflDefn_Norm_v AS rule -- This is the master data profile definition table.

ON

( so.DivCd = rule.DivCd

OR

rule.DivCd = '*')

AND

( so.PlntIdCd = rule.PlntIdCd

OR

rule.PlntIdCd = '*')

AND

( aopf.AtOncePromoFutsAltCd = rule.DmndStreamNm

OR

rule.DmndStreamNm = '*')

AND

( PlngCRD_BusMoRlvncMo = rule.CRD_BusMoOfYrShrtNm

OR

rule.CRD_BusMoOfYrShrtNm = '*')

AND

so.MAD_MondayWkSeqNbr = rule.PerOfstNbr

AND

rule.PerOfstNbr BETWEEN -5 AND 6 -- This identifies the range of weeks we're interested in.

QUALIFY ROW_NUMBER() OVER (PARTITION BY so.DivCd, PseudoPlngCtryCd, so.AtOncePromoFutsCd, so.ProdtCd, PlngCRD_BusMoRlvncMo, so.ShrtLeadTmInd, so.MAD_MondayWkDt ORDER BY rule.DmndStreamNm DESC, rule.CRD_BusMoOfYrShrtNm DESC, rule.DivCd DESC, rule.PlntIdCd DESC) = 1

) AS x

) AS y;

1) First, we lock PLN_T.NK_PerMoToWkCrvExcptnSO for access, we lock

PLN_T.PerDistribPrflDefn for access, and we lock

PLN_T.AtOncePromoFuts for access.

2) Next, we do an all-AMPs RETRIEVE step from

PLN_T.PerDistribPrflDefn by way of an all-rows scan with no

residual conditions into Spool 1 (all_amps), which is built

locally on the AMPs. The size of Spool 1 is estimated with high

confidence to be 2,144 rows (132,928 bytes). The estimated time

for this step is 0.02 seconds.

3) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

way of an all-rows scan with no residual conditions locking for

access into Spool 1 (all_amps), which is built locally on the AMPs.

The size of Spool 1 is estimated with high confidence to be 4,288

rows (265,856 bytes). The estimated time for this step is 0.02

seconds.

4) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

way of an all-rows scan with no residual conditions locking for

access into Spool 1 (all_amps), which is built locally on the AMPs.

The size of Spool 1 is estimated with high confidence to be 6,432

rows (398,784 bytes). The estimated time for this step is 0.02

seconds.

5) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

way of an all-rows scan with no residual conditions locking for

access into Spool 1 (all_amps), which is built locally on the AMPs.

The size of Spool 1 is estimated with high confidence to be 8,576

rows (531,712 bytes). The estimated time for this step is 0.02

seconds.

6) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

way of an all-rows scan with no residual conditions locking for

access into Spool 1 (all_amps), which is built locally on the AMPs.

The size of Spool 1 is estimated with high confidence to be 10,720

rows (664,640 bytes). The estimated time for this step is 0.02

seconds.

7) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

way of an all-rows scan with no residual conditions locking for

access into Spool 1 (all_amps), which is built locally on the AMPs.

The size of Spool 1 is estimated with high confidence to be 12,864

rows (797,568 bytes). The estimated time for this step is 0.02

seconds.

8) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

way of an all-rows scan with no residual conditions locking for

access into Spool 1 (all_amps), which is built locally on the AMPs.

The size of Spool 1 is estimated with high confidence to be 15,008

rows (930,496 bytes). The estimated time for this step is 0.02

seconds.

9) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

way of an all-rows scan with no residual conditions locking for

access into Spool 1 (all_amps), which is built locally on the AMPs.

The size of Spool 1 is estimated with high confidence to be 17,152

rows (1,063,424 bytes). The estimated time for this step is 0.02

seconds.

10) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

way of an all-rows scan with no residual conditions locking for

access into Spool 1 (all_amps), which is built locally on the AMPs.

The size of Spool 1 is estimated with high confidence to be 19,296

rows (1,196,352 bytes). The estimated time for this step is 0.02

seconds.

11) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

way of an all-rows scan with no residual conditions locking for

access into Spool 1 (all_amps), which is built locally on the AMPs.

The size of Spool 1 is estimated with high confidence to be 21,440

rows (1,329,280 bytes). The estimated time for this step is 0.02

seconds.

12) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

way of an all-rows scan with no residual conditions locking for

access into Spool 1 (all_amps), which is built locally on the AMPs.

The size of Spool 1 is estimated with high confidence to be 23,584

rows (1,462,208 bytes). The estimated time for this step is 0.02

seconds.

13) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn

by way of an all-rows scan with no residual conditions locking

for access into Spool 1 (all_amps), which is built locally on

the AMPs. The size of Spool 1 is estimated with high

confidence to be 25,728 rows (1,595,136 bytes). The estimated

time for this step is 0.02 seconds.

2) We do an all-AMPs RETRIEVE step from PLN_T.AtOncePromoFuts by

way of an all-rows scan with no residual conditions into Spool

5 (all_amps) (compressed columns allowed), which is duplicated

on all AMPs. The size of Spool 5 is estimated with high

confidence to be 1,440 rows (44,640 bytes). The estimated

time for this step is 0.02 seconds.

14) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of

an all-rows scan with a condition of ("(rule.PEROFSTNBR <= 6) AND

(rule.PEROFSTNBR >= -5)") into Spool 6 (all_amps) (compressed

columns allowed) fanned out into 50 hash join partitions, which is

duplicated on all AMPs. The size of Spool 6 is estimated with

high confidence to be 9,262,080 rows (574,248,960 bytes). The

estimated time for this step is 0.26 seconds.

15) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an

all-rows scan, which is joined to PLN_T.NK_PerMoToWkCrvExcptnSO by

way of an all-rows scan with no residual conditions. Spool 5 and

PLN_T.NK_PerMoToWkCrvExcptnSO are joined using a dynamic hash join,

with a join condition of (

"PLN_T.NK_PerMoToWkCrvExcptnSO.AtOncePromoFutsCd =

AtOncePromoFutsCd"). The result goes into Spool 7 (all_amps)

(compressed columns allowed) fanned out into 50 hash join

partitions, which is built locally on the AMPs. The size of Spool

7 is estimated with low confidence to be 46,605,984 rows (

8,342,471,136 bytes). The estimated time for this step is 3.42

seconds.

16) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an

all-rows scan, which is joined to Spool 7 (Last Use) by way of an

all-rows scan. Spool 6 and Spool 7 are right outer joined using a

hash join of 50 partitions, with condition(s) used for

non-matching on right table ("(MAD_MondayWkSeqNbr <= 6) AND

(MAD_MondayWkSeqNbr >= -5)"), with a join condition of (

"(MAD_MondayWkSeqNbr = PEROFSTNBR) AND ((((( CASE WHEN

((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 1) THEN ('Jan')

WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 2) THEN

('Feb') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 3)

THEN ('Mar') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))=

4) THEN ('Apr') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt

)))= 5) THEN ('May') WHEN ((EXTRACT(MONTH FROM

(PlngCRD_BusMoRlvncDt )))= 6) THEN ('Jun') WHEN ((EXTRACT(MONTH

FROM (PlngCRD_BusMoRlvncDt )))= 7) THEN ('Jul') WHEN

((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 8) THEN ('Aug')

WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 9) THEN

('Sep') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 10)

THEN ('Oct') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))=

11) THEN ('Nov') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt

)))= 12) THEN ('Dec') ELSE (NULL) END ))= CRD_BUSMOOFYRSHRTNM) OR

(CRD_BUSMOOFYRSHRTNM = '* ')) AND (((PlntIdCd =PLNTIDCD) OR

(PLNTIDCD = '* ')) AND (((DivCd = DIVCD) OR (DIVCD = '* ')) AND

((AtOncePromoFutsAltCd = DMNDSTREAMNM) OR (DMNDSTREAMNM = '*

')))))"). The result goes into Spool 4 (all_amps) (compressed

columns allowed), 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 99,923,229,696 rows (

22,482,726,681,600 bytes). The estimated time for this step is 1

hour and 51 minutes.

17) We do an all-AMPs STAT FUNCTION step from Spool 4 (Last Use) by

way of an all-rows scan into Spool 12 (Last Use), which is assumed

to be redistributed by value to all AMPs. The result rows are put

into Spool 10 (all_amps) (compressed columns allowed), which is

built locally on the AMPs. The size is estimated with low

confidence to be 99,923,229,696 rows (33,873,974,866,944 bytes).

18) We do an all-AMPs RETRIEVE step from Spool 10 (Last Use) by way of

an all-rows scan with a condition of ("Field_21 = 1") into Spool 2

(used to materialize view, derived table, table function or table

operator x) (all_amps) (compressed columns allowed), which is

built locally on the AMPs. The result spool file will not be

cached in memory. The size of Spool 2 is estimated with low

confidence to be 99,923,229,696 rows (19,884,722,709,504 bytes).

The estimated time for this step is 1 hour and 56 minutes.

19) We do an all-AMPs STAT FUNCTION step from Spool 2 (Last Use) by

way of an all-rows scan into Spool 18 (Last Use), which is

redistributed by hash code to all AMPs. The result rows are put

into Spool 16 (all_amps) (compressed columns allowed), which is

built locally on the AMPs. The size is estimated with low

confidence to be 99,923,229,696 rows (30,876,277,976,064 bytes).

The estimated time for this step is 2 hours and 3 minutes.

20) We do an all-AMPs RETRIEVE step from Spool 16 (Last Use) by way of

an all-rows scan into Spool 3 (used to materialize view, derived

table, table function or table operator y) (all_amps) (compressed

columns allowed), which is built locally on the AMPs. The result

spool file will not be cached in memory. The size of Spool 3 is

estimated with low confidence to be 99,923,229,696 rows (

20,284,415,628,288 bytes). The estimated time for this step is 2

hours and 6 minutes.

21) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of

an all-rows scan into Spool 21 (group_amps), which is built

locally on the AMPs. The result spool file will not be cached in

memory. The size of Spool 21 is estimated with low confidence to

be 99,923,229,696 rows (30,576,508,286,976 bytes). The estimated

time for this step is 2 hours and 6 minutes.

22) Finally, we send out an END TRANSACTION step to all AMPs involved

in processing the request.

-> The contents of Spool 21 are sent back to the user as the result

of statement 1.