 |
 |
Archives of the TeradataForum
Message Posted: Mon, 29 Sep 2014 @ 20:47:36 GMT
| Subj: | | Re: Same query - Different Explains |
| |
| From: | | Bakthavachalam, Roopalini |
Dieter - Here is the query and the Explain
WITH F_PRPTY_ACCT_TBL (ACCT_DMNSN_ID,
CAL_DT,CRNCY_CD,
ACTL_FCT_NR,
FRCST_FCT_NR,
BDGT_FCT_NR,
HTL_CD,
VRSN_MBR_NM) AS
( SELECT ACCT_DMNSN_ID,
CAST(CAL_DT AS CHAR(10)) AS CAL_DT,
CRNCY_CD,
ACTL_FCT_NR,
FRCST_FCT_NR,
BDGT_FCT_NR,
HTL_CD,
VRSN_MBR_NM
FROM BAKTHRO_TEST.F_PRPTY_ACCT
)
SELECT
SUM(COALESCE (FCT_NR,0)) AS CNT
FROM ( SELECT D_ACCT_DMNSN.ACCT_DMNSN_ID,
L_F_CUBE_ROW_WRKNG.PRD_MBR_NM,
L_F_CUBE_ROW_WRKNG.ENT_MBR_NM,
L_F_CUBE_ROW_WRKNG.ACCT_MBR_NM,
L_F_CUBE_ROW_WRKNG.DEPT_MBR_NM,
L_F_CUBE_ROW_WRKNG.CLS_MBR_NM,
L_F_CUBE_ROW_WRKNG.CTGRY_MBR_NM,
L_F_CUBE_ROW_WRKNG.DTL_MBR_NM,
L_F_CUBE_ROW_WRKNG.FOREX_MBR_NM,
L_F_CUBE_ROW_WRKNG.VRSN_MBR_NM,
L_F_CUBE_ROW_WRKNG.DAY_MBR_NM,
L_F_CUBE_ROW_WRKNG.FCT_NR FCT_NR,
'20'||SUBSTR(TRIGGER_TBL.YR_MBR_NM,3,2)||'-'||
CASE WHEN L_F_CUBE_ROW_WRKNG.PRD_MBR_NM='JAN' THEN '01'
WHEN L_F_CUBE_ROW_WRKNG.PRD_MBR_NM='FEB' THEN '02'
WHEN L_F_CUBE_ROW_WRKNG.PRD_MBR_NM='MAR' THEN '03'
WHEN L_F_CUBE_ROW_WRKNG.PRD_MBR_NM='APR' THEN '04'
WHEN L_F_CUBE_ROW_WRKNG.PRD_MBR_NM='MAY' THEN '05'
WHEN L_F_CUBE_ROW_WRKNG.PRD_MBR_NM='JUN' THEN '06'
WHEN L_F_CUBE_ROW_WRKNG.PRD_MBR_NM='JUL' THEN '07'
WHEN L_F_CUBE_ROW_WRKNG.PRD_MBR_NM='AUG' THEN '08'
WHEN L_F_CUBE_ROW_WRKNG.PRD_MBR_NM='SEP' THEN '09'
WHEN L_F_CUBE_ROW_WRKNG.PRD_MBR_NM='OCT' THEN '10'
WHEN L_F_CUBE_ROW_WRKNG.PRD_MBR_NM='NOV' THEN '11'
WHEN L_F_CUBE_ROW_WRKNG.PRD_MBR_NM='DEC' THEN '12'
END ||'-'|| L_F_CUBE_ROW_WRKNG.DAY_MBR_NM AS CAL_DT,
CASE WHEN TRIGGER_TBL.MTH_MBR_NM IS NULL
THEN L_F_CUBE_ROW_WRKNG.PRD_MBR_NM
ELSE TRIGGER_TBL.MTH_MBR_NM
END AS CASE_1
FROM
BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG L_F_CUBE_ROW_WRKNG,
( SELECT DISTINCT
ENT_MBR_NM,
VRSN_MBR_NM,
SCNRO_MBR_NM,
YR_MBR_NM,
MTH_MBR_NM,
BTCH_ID,
GRAIN
FROM BAKTHRO_TEST.TR_PRPTY_TRGR
WHERE BTCH_ID = 10
AND GRAIN='DAILY'
) TRIGGER_TBL ,
BAKTHRO_TEST.D_ETL_KPIS KPIS,
BAKTHRO_TEST.D_ACCT_DMNSND_ACCT_DMNSN
WHERE L_F_CUBE_ROW_WRKNG.ACCT_MBR_NM = KPIS.ACCT_MBR_NM
AND L_F_CUBE_ROW_WRKNG.DEPT_MBR_NM = KPIS.DEPT_MBR_NM
AND L_F_CUBE_ROW_WRKNG.DTL_MBR_NM = KPIS.DTL_MBR_NM
AND L_F_CUBE_ROW_WRKNG.CLS_MBR_NM = KPIS.CLS_MBR_NM
AND L_F_CUBE_ROW_WRKNG.CTGRY_MBR_NM = KPIS.CTGRY_MBR_NM
AND L_F_CUBE_ROW_WRKNG.ACCT_MBR_NM=D_ACCT_DMNSN.ACCT_MBR_NM
AND L_F_CUBE_ROW_WRKNG.DEPT_MBR_NM=D_ACCT_DMNSN.DEPT_MBR_NM
AND L_F_CUBE_ROW_WRKNG.CLS_MBR_NM=D_ACCT_DMNSN.CLS_MBR_NM
AND L_F_CUBE_ROW_WRKNG.CTGRY_MBR_NM=D_ACCT_DMNSN.CTGRY_MBR_NM
AND L_F_CUBE_ROW_WRKNG.DTL_MBR_NM=D_ACCT_DMNSN.DTL_MBR_NM
AND L_F_CUBE_ROW_WRKNG.ENT_MBR_NM = TRIGGER_TBL.ENT_MBR_NM
AND L_F_CUBE_ROW_WRKNG.VRSN_MBR_NM = TRIGGER_TBL.VRSN_MBR_NM
AND L_F_CUBE_ROW_WRKNG.SCNRO_MBR_NM =TRIGGER_TBL.SCNRO_MBR_NM
AND L_F_CUBE_ROW_WRKNG.YR_MBR_NM = TRIGGER_TBL.YR_MBR_NM
AND KPIS.KPI_PERIOD='D'
AND CASE_1 = L_F_CUBE_ROW_WRKNG.PRD_MBR_NM
AND L_F_CUBE_ROW_WRKNG.BTCH_ID = 10
) A
INNER JOIN F_PRPTY_ACCT_TBL B
ON A.CAL_DT=B.CAL_DT
AND A.ENT_MBR_NM=B.HTL_CD
AND A.ACCT_DMNSN_ID=B.ACCT_DMNSN_ID
AND A.VRSN_MBR_NM=B.VRSN_MBR_NM
EXPLAIN
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct BAKTHRO_TEST."pseudo table" for read on a RowHash to prevent global deadlock for
BAKTHRO_TEST.F_PRPTY_ACCT_TJI004.
| |
| | 2) | Next, we lock a distinct BAKTHRO_TEST."pseudo table" for read on a RowHash to prevent global deadlock for BAKTHRO_TEST.D_ACCT_DMNSN.
| |
| | 3) | We lock a distinct BAKTHRO_TEST."pseudo table" for read on a RowHash to prevent global deadlock for BAKTHRO_TEST.KPIS.
| |
| | 4) | We lock a distinct BAKTHRO_TEST."pseudo table" for read on a RowHash to prevent global deadlock for BAKTHRO_TEST.TR_PRPTY_TRGR.
| |
| | 5) | We lock a distinct BAKTHRO_TEST."pseudo table" for read on a RowHash to prevent global deadlock for BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG.
| |
| | 6) | We lock BAKTHRO_TEST.F_PRPTY_ACCT_TJI004 in view F_PRPTY_ACCT_TBL.B for read, we lock BAKTHRO_TEST.D_ACCT_DMNSN for read, we lock
BAKTHRO_TEST.KPIS for read, we lock BAKTHRO_TEST.TR_PRPTY_TRGR for read, and we lock BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG for read.
| |
| | 7) | We do an all-AMPs SUM step to aggregate from BAKTHRO_TEST.TR_PRPTY_TRGR by way of an all-rows scan with a condition of
("(BAKTHRO_TEST.TR_PRPTY_TRGR.BTCH_ID = 10) AND (BAKTHRO_TEST.TR_PRPTY_TRGR.GRAIN = 'DAILY')") , grouping by field1 (
BAKTHRO_TEST.TR_PRPTY_TRGR.ENT_MBR_NM ,BAKTHRO_TEST.TR_PRPTY_TRGR.VRSN_MBR_NM ,BAKTHRO_TEST.TR_PRPTY_TRGR.SCNRO_MBR_NM
,BAKTHRO_TEST.TR_PRPTY_TRGR.YR_MBR_NM ,BAKTHRO_TEST.TR_PRPTY_TRGR.MTH_MBR_NM ,BAKTHRO_TEST.TR_PRPTY_TRGR.BTCH_ID
,BAKTHRO_TEST.TR_PRPTY_TRGR.GRAIN). Aggregate Intermediate Results are computed locally, then placed in Spool 3. The size of Spool 3 is
estimated with no confidence to be 241 rows (194,728 bytes). The estimated time for this step is 0.02 seconds.
| |
| | 8) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan into Spool 1 (used to materialize view, derived table
or table function TRIGGER_TBL) (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 241 rows
(47,236 bytes). The estimated time for this step is 0.02 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from BAKTHRO_TEST.KPIS by way of an all-rows scan with a condition of ( "BAKTHRO_TEST.KPIS.KPI_PERIOD =
'D'") into Spool 8 (all_amps), which is duplicated on all AMPs. The size of Spool 8 is estimated with high confidence to be 528 rows (27,984
bytes). The estimated time for this step is 0.02 seconds.
| | |
| | 9) | We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of an all-rows scan, which is joined to BAKTHRO_TEST.D_ACCT_DMNSN by way of an
all-rows scan with no residual conditions. Spool 8 and BAKTHRO_TEST.D_ACCT_DMNSN are joined using a single partition hash_ join, with a join
condition of ("(ACCT_MBR_NM = BAKTHRO_TEST.D_ACCT_DMNSN.ACCT_MBR_NM) AND ((DEPT_MBR_NM = BAKTHRO_TEST.D_ACCT_DMNSN.DEPT_MBR_NM) AND ((DTL_MBR_NM =
BAKTHRO_TEST.D_ACCT_DMNSN.DTL_MBR_NM) AND ((CLS_MBR_NM = BAKTHRO_TEST.D_ACCT_DMNSN.CLS_MBR_NM) AND (CTGRY_MBR_NM =
BAKTHRO_TEST.D_ACCT_DMNSN.CTGRY_MBR_NM ))))"). The result goes into Spool 9 (all_amps), which is duplicated on all AMPs. The size of Spool 9 is
estimated with low confidence to be 720 rows ( 167,040 bytes). The estimated time for this step is 0.03 seconds.
| |
| | 10) | We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of an all-rows scan, which is joined to BAKTHRO_TEST.F_PRPTY_ACCT_TJI004 in
view F_PRPTY_ACCT_TBL.B by way of an all-rows scan with no residual conditions. Spool 9 and BAKTHRO_TEST.F_PRPTY_ACCT_TJI004 are joined using a
single partition hash_ join, with a join condition of ("ACCT_DMNSN_ID = BAKTHRO_TEST.F_PRPTY_ACCT_TJI004.ACCT_DMNSN_ID"). The result goes into
Spool 10 (all_amps), which is duplicated on all AMPs. The size of Spool 10 is estimated with index join confidence to be 72,720 rows (19,998,000
bytes). The estimated time for this step is 0.10 seconds.
| |
| | 11) | We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan into Spool 11 (all_amps), which is duplicated on all
AMPs. The size of Spool 11 is estimated with no confidence to be 11,568 rows (2,186,352 bytes). The estimated time for this step is 0.02
seconds.
| |
| | 12) | We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of an all-rows scan, which is joined to BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG by way
of an all-rows scan with a condition of ( "BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG.BTCH_ID = 10"). Spool 10 and BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG are
joined using a single partition hash_ join, with a join condition of ( "(BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG.CTGRY_MBR_NM = CTGRY_MBR_NM) AND
((BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG.CLS_MBR_NM = CLS_MBR_NM) AND ((BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG.DTL_MBR_NM = DTL_MBR_NM) AND
((BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG.DEPT_MBR_NM = DEPT_MBR_NM) AND ((BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG.ACCT_MBR_NM = ACCT_MBR_NM) AND
((BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG.ACCT_MBR_NM = ACCT_MBR_NM) AND ((BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG.DEPT_MBR_NM = DEPT_MBR_NM) AND
((BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG.DTL_MBR_NM = DTL_MBR_NM) AND ((BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG.CLS_MBR_NM = CLS_MBR_NM) AND
((BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG.CTGRY_MBR_NM = CTGRY_MBR_NM) AND ((BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG.VRSN_MBR_NM = VRSN_MBR_NM) AND
(BAKTHRO_TEST.L_F_CUBE_ROW_WRKNG.ENT_MBR_NM = HTL_CD )))))))))))"). The result goes into Spool 12 (all_amps), which is built locally on the AMPs.
The size of Spool 12 is estimated with index join confidence to be 97,292 rows (27,047,176 bytes). The estimated time for this step is 0.30
seconds.
| |
| | 13) | We do an all-AMPs JOIN step from Spool 11 (Last Use) by way of an all-rows scan, which is joined to Spool 12 (Last Use) by way of an all-
rows scan. Spool 11 and Spool 12 are joined using a single partition hash join, with a join condition of ("(VRSN_MBR_NM = VRSN_MBR_NM) AND
((HTL_CD = ENT_MBR_NM) AND ((((((TRANSLATE((('20'||(SUBSTR(YR_MBR_NM ,3 ,2 )))||'-')USING LATIN_TO_UNICODE))||(( CASE WHEN (PRD_MBR_NM = 'JAN')
THEN ('01') WHEN (PRD_MBR_NM = 'FEB') THEN ('02') WHEN (PRD_MBR_NM = 'MAR') THEN ('03') WHEN (PRD_MBR_NM = 'APR') THEN ('04') WHEN (PRD_MBR_NM =
'MAY') THEN ('05') WHEN (PRD_MBR_NM = 'JUN') THEN ('06') WHEN (PRD_MBR_NM = 'JUL') THEN ('07') WHEN (PRD_MBR_NM = 'AUG') THEN ('08') WHEN
(PRD_MBR_NM = 'SEP') THEN ('09') WHEN (PRD_MBR_NM = 'OCT') THEN ('10') WHEN (PRD_MBR_NM = 'NOV') THEN ('11') WHEN (PRD_MBR_NM = 'DEC') THEN ('12')
ELSE (NULL) END )))||'-')||(TRANSLATE((DAY_MBR_NM )USING LATIN_TO_UNICODE)))= (TRANSLATE((TRIM(BOTH FROM {RightTable}.CAL_DT (CHAR(10), CHARACTER
SET LATIN, NOT CASESPECIFIC, FORMAT 'YYYY-MM-DD'))(CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC))USING LATIN_TO_UNICODE))) AND (((( CASE WHEN
(MTH_MBR_NM IS NULL) THEN (PRD_MBR_NM) ELSE (MTH_MBR_NM) END ))= PRD_MBR_NM) AND ((YR_MBR_NM = YR_MBR_NM) AND ((SCNRO_MBR_NM = SCNRO_MBR_NM) AND
((VRSN_MBR_NM = VRSN_MBR_NM) AND (ENT_MBR_NM = ENT_MBR_NM )))))))"). 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 97,292 rows (2,237,716 bytes). The estimated time for this step is 0.07 seconds.
| |
| | 14) | We do an all-AMPs SUM step to aggregate from Spool 7 (Last Use) by way of an all-rows scan , grouping by field1 ( 7578). Aggregate
Intermediate Results are computed globally, then placed in Spool 13. The size of Spool 13 is estimated with high confidence to be 1 row (23
bytes). The estimated time for this step is 0.04 seconds.
| |
| | 15) | We do an all-AMPs RETRIEVE step from Spool 13 (Last Use) by way of an all-rows scan into Spool 5 (group_amps), which is built locally on
the AMPs. The size of Spool 5 is estimated with high confidence to be 1 row (29 bytes). The estimated time for this step is 0.01 seconds.
| |
| | 16) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 5 are sent back to the user as the result of statement 1. The total estimated time is 0.62 seconds.
| |
The same query in production (with a bigger row count) is running under 4 sec and it gives a different EXPLAIN
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct BAKTHRO."pseudo table" for read on a RowHash to prevent global deadlock for BAKTHRO.F_PRPTY_ACCT_TJI004.
| |
| | 2) | Next, we lock a distinct BAKTHRO."pseudo table" for read on a RowHash to prevent global deadlock for BAKTHRO.D_ACCT_DMNSN.
| |
| | 3) | We lock a distinct BAKTHRO."pseudo table" for read on a RowHash to prevent global deadlock for BAKTHRO.KPIS.
| |
| | 4) | We lock a distinct BAKTHRO."pseudo table" for read on a RowHash to prevent global deadlock for BAKTHRO.TR_PRPTY_TRGR.
| |
| | 5) | We lock a distinct BAKTHRO."pseudo table" for read on a RowHash to prevent global deadlock for BAKTHRO.L_F_CUBE_ROW_WRKNG.
| |
| | 6) | We lock BAKTHRO.F_PRPTY_ACCT_TJI004 in view F_PRPTY_ACCT_TBL.B for read, we lock BAKTHRO.D_ACCT_DMNSN for read, we lock BAKTHRO.KPIS for
read, we lock BAKTHRO.TR_PRPTY_TRGR for read, and we lock BAKTHRO.L_F_CUBE_ROW_WRKNG for read.
| |
| | 7) | We do an all-AMPs SUM step to aggregate from BAKTHRO.TR_PRPTY_TRGR by way of an all-rows scan with a condition of (
"(BAKTHRO.TR_PRPTY_TRGR.BTCH_ID = 10) AND (BAKTHRO.TR_PRPTY_TRGR.GRAIN = 'DAILY')") , grouping by field1 ( BAKTHRO.TR_PRPTY_TRGR.ENT_MBR_NM
,BAKTHRO.TR_PRPTY_TRGR.VRSN_MBR_NM ,BAKTHRO.TR_PRPTY_TRGR.SCNRO_MBR_NM ,BAKTHRO.TR_PRPTY_TRGR.YR_MBR_NM ,BAKTHRO.TR_PRPTY_TRGR.MTH_MBR_NM
,BAKTHRO.TR_PRPTY_TRGR.BTCH_ID ,BAKTHRO.TR_PRPTY_TRGR.GRAIN). Aggregate Intermediate Results are computed locally, then placed in Spool 3. The
size of Spool 3 is estimated with low confidence to be 24 rows (19,392 bytes). The estimated time for this step is 0.06 seconds.
| |
| | 8) | We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan into Spool 1 (used to materialize view, derived table or
table function TRIGGER_TBL) (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 24 rows
(4,704 bytes). The estimated time for this step is 0.02 seconds.
| |
| | 9) | We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan into Spool 8 (all_amps), which is duplicated on all
AMPs. The size of Spool 8 is estimated with low confidence to be 1,152 rows (216,576 bytes). The estimated time for this step is 0.02 seconds.
| |
| | 10) | We do an all-AMPs JOIN step from BAKTHRO.L_F_CUBE_ROW_WRKNG by way of an all-rows scan with a condition of (
"BAKTHRO.L_F_CUBE_ROW_WRKNG.BTCH_ID = 10"), which is joined to Spool 8 (Last Use) by way of an all-rows scan. BAKTHRO.L_F_CUBE_ROW_WRKNG and
Spool 8 are joined using a product join, with a join condition of ("((( CASE WHEN (MTH_MBR_NM IS NULL) THEN
(BAKTHRO.L_F_CUBE_ROW_WRKNG.PRD_MBR_NM) ELSE (MTH_MBR_NM) END ))= BAKTHRO.L_F_CUBE_ROW_WRKNG.PRD_MBR_NM) AND
((BAKTHRO.L_F_CUBE_ROW_WRKNG.YR_MBR_NM = YR_MBR_NM) AND ((BAKTHRO.L_F_CUBE_ROW_WRKNG.SCNRO_MBR_NM = SCNRO_MBR_NM) AND
((BAKTHRO.L_F_CUBE_ROW_WRKNG.VRSN_MBR_NM = VRSN_MBR_NM) AND (BAKTHRO.L_F_CUBE_ROW_WRKNG.ENT_MBR_NM = ENT_MBR_NM ))))"). The result goes into
Spool 9 (all_amps), which is redistributed by the hash code of (BAKTHRO.L_F_CUBE_ROW_WRKNG.ACCT_MBR_NM, BAKTHRO.L_F_CUBE_ROW_WRKNG.DEPT_MBR_NM,
BAKTHRO.L_F_CUBE_ROW_WRKNG.DTL_MBR_NM, 'D') to all AMPs. Then we do a SORT to order Spool 9 by row hash. The size of Spool 9 is estimated with
low confidence to be 76 rows (33,592 bytes). The estimated time for this step is 0.12 seconds.
| |
| | 11) | We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of a RowHash match scan, which is joined to BAKTHRO.KPIS by way of a RowHash
match scan with a condition of ("BAKTHRO.KPIS.KPI_PERIOD = 'D'"). Spool 9 and BAKTHRO.KPIS are joined using a merge join, with a join condition
of ("(CTGRY_MBR_NM = BAKTHRO.KPIS.CTGRY_MBR_NM) AND ((CLS_MBR_NM = BAKTHRO.KPIS.CLS_MBR_NM) AND ((DTL_MBR_NM = BAKTHRO.KPIS.DTL_MBR_NM) AND
((DEPT_MBR_NM = BAKTHRO.KPIS.DEPT_MBR_NM) AND (ACCT_MBR_NM = BAKTHRO.KPIS.ACCT_MBR_NM ))))"). The result goes into Spool 10 (all_amps), which is
duplicated on all AMPs. The size of Spool 10 is estimated with low confidence to be 864 rows (415,584 bytes). The estimated time for this step is
0.03 seconds.
| |
| | 12) | We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of an all-rows scan, which is joined to BAKTHRO.D_ACCT_DMNSN by way of an all-
rows scan with a condition of ("NOT (BAKTHRO.D_ACCT_DMNSN.DEPT_MBR_NM IS NULL)"). Spool 10 and BAKTHRO.D_ACCT_DMNSN are joined using a single
partition hash_join, with a join condition of ("(CTGRY_MBR_NM = BAKTHRO.D_ACCT_DMNSN.CTGRY_MBR_NM) AND ((CLS_MBR_NM =
BAKTHRO.D_ACCT_DMNSN.CLS_MBR_NM) AND ((DTL_MBR_NM = BAKTHRO.D_ACCT_DMNSN.DTL_MBR_NM) AND ((DEPT_MBR_NM = BAKTHRO.D_ACCT_DMNSN.DEPT_MBR_NM) AND
((ACCT_MBR_NM = BAKTHRO.D_ACCT_DMNSN.ACCT_MBR_NM) AND ((CTGRY_MBR_NM = BAKTHRO.D_ACCT_DMNSN.CTGRY_MBR_NM) AND ((CLS_MBR_NM =
BAKTHRO.D_ACCT_DMNSN.CLS_MBR_NM) AND ((DTL_MBR_NM = BAKTHRO.D_ACCT_DMNSN.DTL_MBR_NM) AND ((DEPT_MBR_NM = BAKTHRO.D_ACCT_DMNSN.DEPT_MBR_NM) AND
(ACCT_MBR_NM = BAKTHRO.D_ACCT_DMNSN.ACCT_MBR_NM )))))))))"). The result goes into Spool 11 (all_amps), which is duplicated on all AMPs. The size
of Spool 11 is estimated with low confidence to be 1,152 rows (312,192 bytes). The estimated time for this step is 0.03 seconds.
| |
| | 13) | We do an all-AMPs RETRIEVE step from BAKTHRO.F_PRPTY_ACCT_TJI004 in view F_PRPTY_ACCT_TBL.B by way of an all-rows scan with no residual
conditions into Spool 12 (all_amps), which is built locally on the AMPs. The size of Spool 12 is estimated with high confidence to be 1,196,901
rows (77,798,565 bytes). The estimated time for this step is 0.35 seconds.
| |
| | 14) | We do an all-AMPs JOIN step from Spool 11 (Last Use) by way of an all-rows scan, which is joined to Spool 12 (Last Use) by way of an all-
rows scan. Spool 11 and Spool 12 are joined using a single partition hash join, with a join condition of ("(VRSN_MBR_NM = VRSN_MBR_NM) AND
((HTL_CD = ENT_MBR_NM) AND ((((((TRANSLATE((('20'||(SUBSTR(YR_MBR_NM ,3 ,2 )))||'-')USING LATIN_TO_UNICODE))||(( CASE WHEN (PRD_MBR_NM = 'JAN')
THEN ('01') WHEN (PRD_MBR_NM = 'FEB') THEN ('02') WHEN (PRD_MBR_NM = 'MAR') THEN ('03') WHEN (PRD_MBR_NM = 'APR') THEN ('04') WHEN (PRD_MBR_NM =
'MAY') THEN ('05') WHEN (PRD_MBR_NM = 'JUN') THEN ('06') WHEN (PRD_MBR_NM = 'JUL') THEN ('07') WHEN (PRD_MBR_NM = 'AUG') THEN ('08') WHEN
(PRD_MBR_NM = 'SEP') THEN ('09') WHEN (PRD_MBR_NM = 'OCT') THEN ('10') WHEN (PRD_MBR_NM = 'NOV') THEN ('11') WHEN (PRD_MBR_NM = 'DEC') THEN ('12')
ELSE (NULL) END )))||'-')||(TRANSLATE((DAY_MBR_NM )USING LATIN_TO_UNICODE)))= (TRANSLATE((TRIM(BOTH FROM {RightTable}.CAL_DT (CHAR(10), CHARACTER
SET LATIN, NOT CASESPECIFIC, FORMAT 'YYYY-MM-DD'))(CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC))USING LATIN_TO_UNICODE))) AND ((VRSN_MBR_NM =
VRSN_MBR_NM) AND ((ENT_MBR_NM = HTL_CD) AND (ACCT_DMNSN_ID = ACCT_DMNSN_ID )))))"). The result goes into Spool 7 (all_amps), which is built
locally on the AMPs. The size of Spool 7 is estimated with index join confidence to be 15,741 rows (362,043 bytes). The estimated time for this
step is 0.11 seconds.
| |
| | 15) | We do an all-AMPs SUM step to aggregate from Spool 7 (Last Use) by way of an all-rows scan , grouping by field1 ( 9180). Aggregate
Intermediate Results are computed globally, then placed in Spool 13. The size of Spool 13 is estimated with high confidence to be 1 row (23
bytes). The estimated time for this step is 0.03 seconds.
| |
| | 16) | We do an all-AMPs RETRIEVE step from Spool 13 (Last Use) by way of an all-rows scan into Spool 5 (group_amps), which is built locally on
the AMPs. The size of Spool 5 is estimated with high confidence to be 1 row (29 bytes). The estimated time for this step is 0.01 seconds.
| |
| | 17) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 5 are sent back to the user as the result of statement 1. The total estimated time is 0.79 seconds.
| |
| |
