 |
 |
Archives of the TeradataForum
Message Posted: Thu, 05 Oct 2000 @ 14:22:32 GMT
| Subj: | | Where clause VS small table #2 |
| |
| From: | | Rudel Simard |
The next of the explain
----------------------------------------
-- SCRIPT OF STEP 4 --------------------
----------------------------------------
SELECT SERV.SMOD_COD_CLA_DISP,
SERV.SMOD_NO_DISP,
SUM(CASE
WHEN (SERV.SMOD_COD_STA_DECIS_DEM_PAIMT = 'PPY')
THEN SERV.SMOD_MNT_DEM_KM
ELSE SERV.SMOD_MNT_PAIMT_KM
END) AS Cout_total_acte
FROM SYSDBA.I_KT_SMOD_MD_KM AS SERV
INNER JOIN P_A1GEII.IDENT_MNT_TOT_REMUN_FIXE AS PROF ON
SERV.SMOD_COD_CLA_DISP = PROF.COD_CLA_DISP AND
SERV.SMOD_NO_DISP = PROF.NO_DISP AND
SERV.SMOD_DAT_KM BETWEEN PROF.Begin_date_EXTRAC AND PROF.End_date_EXTRAC
WHERE ((SERV.SMOD_COD_STA_DECIS_DEM_PAIMT = 'PAY' AND SERV.SMOD_MNT_PAIMT_KM > 0)
OR (SERV.SMOD_COD_STA_DECIS_DEM_PAIMT = 'PPY' AND SERV.SMOD_MNT_DEM_KM > 0))
GROUP BY SERV.SMOD_COD_CLA_DISP,
SERV.SMOD_NO_DISP
;
*** Query completed. No rows found.
*** Total elapsed time was 3 minutes and 37 seconds.
Explanation
-------------------------------------------------- | |
| | 5) | We do an all-AMPs RETRIEVE step from DONNE.TDEM_PAIMT_ACTE_MD by way of an all-rows scan with a condition of
("(((DONNE.TDEM_PAIMT_ACTE_MD.COD_STA_DECIS_DEM_PAIMT = 'PAY') AND (DONNE.TDEM_PAIMT_ACTE_MD.MNT_PAIMT_KM > 0.00 )) OR
((DONNE.TDEM_PAIMT_ACTE_MD.COD_STA_DECIS_DEM_PAIMT = 'PPY') AND (DONNE.TDEM_PAIMT_ACTE_MD.MNT_DEM_KM > 0.00 ))) AND
(DONNE.TDEM_PAIMT_ACTE_MD.DAT_KM > 0)") into Spool 2, which is built locally on the AMPs. The size of Spool 2 is estimated with high
confidence to be 71,217 rows. The estimated time for this step is 23 minutes and 58 seconds.
| |
| | 6) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan, which is joined to DONNE.TSERV_REND_ACTE_MD.
Spool 2 and DONNE.TSERV_REND_ACTE_MD are joined using a merge join, with a join condition of ("(DONNE.TSERV_REND_ACTE_MD.COD_STA_DECIS =
Spool_2.COD_STA_DECIS_DEM_PAIMT) AND ((Spool_2.NCI = DONNE.TSERV_REND_ACTE_MD.NCI) AND (Spool_2.NO_INDIV_BEN =
DONNE.TSERV_REND_ACTE_MD.NO_INDIV_BEN ))"). The input table DONNE.TSERV_REND_ACTE_MD will not be cached in memory. The result goes into
Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 71,217 rows. The estimated time
for this step is 1 minute and 16 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from P_A1GEII.PROF by way of an all-rows scan with no residual conditions into Spool 5, which is
duplicated on all AMPs. The size of Spool 5 is estimated with high confidence to be 28 rows. The estimated time for this step is 0.16
seconds.
| | |
| | 7) | We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an all-rows scan, which is joined to Spool 1 (Last Use) with a
condition of ("((Spool_1.SMOD_COD_STA_DECIS_DEM_PAIMT = 'PAY') AND (Spool_1.SMOD_MNT_PAIMT_KM > 0.00 )) OR
((Spool_1.SMOD_COD_STA_DECIS_DEM_PAIMT = 'PPY') AND (Spool_1.SMOD_MNT_DEM_KM > 0.00 ))"). Spool 5 and Spool 1 are joined using a product
join, with a join condition of ("(Spool_1.SMOD_DAT_KM >= Spool_5.Begin_date_EXTRAC) AND ((Spool_1.SMOD_DAT_KM <= Spool_5.End_date_EXTRAC)
AND ((Spool_1.SMOD_NO_DISP = Spool_5.NO_DISP) AND (Spool_1.SMOD_COD_CLA_DISP = Spool_5.COD_CLA_DISP )))"). The result goes into Spool 4,
which is built locally on the AMPs. The size of Spool 4 is estimated with index join confidence to be 71,217 rows. The estimated time for
this step is 0.83 seconds.
| |
| | 8) | We do a SUM step to aggregate from Spool 4 (Last Use) by way of an all-rows scan, and the grouping identifier in field 1. Aggregate
Intermediate Results are computed globally, then placed in Spool 6. The size of Spool 6 is estimated to be 267 rows.
| |
| | 9) | We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of an all-rows scan into Spool 3, which is built locally on the AMPs.
The size of Spool 3 is estimated with no confidence to be 267 to 71,217 rows. The estimated time for this step is 0.17 to 0.58 seconds.
| |
| | 10) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 3 are sent back to the user as the result of statement 1.
| |
----------------------------------------
-- SCRIPT OF STEP 5 --------------------
----------------------------------------
REPLACE VIEW SYSDBA.I_KT_SMOD_MD_KM AS
SELECT
TDEM_PAIMT_ACTE_MD.COD_CLA_DISP (NAMED SMOD_COD_CLA_DISP),
TSERV_REND_ACTE_MD.COD_ENTEN (NAMED SMOD_COD_ENTEN),
TSERV_REND_ACTE_MD.COD_LOCAL (NAMED SMOD_COD_LOCAL),
TSERV_REND_ACTE_MD.COD_SPEC (NAMED SMOD_COD_SPEC),
TDEM_PAIMT_ACTE_MD.COD_STA_DECIS_DEM_PAIMT (NAMED
SMOD_COD_STA_DECIS_DEM_PAIMT),
TDEM_PAIMT_ACTE_MD.DAT_KM (NAMED SMOD_DAT_KM),
TDEM_PAIMT_ACTE_MD.MNT_DEM_KM (NAMED SMOD_MNT_DEM_KM),
TDEM_PAIMT_ACTE_MD.MNT_PAIMT_KM (NAMED SMOD_MNT_PAIMT_KM),
TDEM_PAIMT_ACTE_MD.NCI (NAMED SMOD_NCI),
TDEM_PAIMT_ACTE_MD.NO_DISP (NAMED SMOD_NO_DISP),
TSERV_REND_ACTE_MD.NO_ETAB_USUEL (NAMED SMOD_NO_ETAB_USUEL),
TDEM_PAIMT_ACTE_MD.NO_INDIV_BEN (NAMED SMOD_NO_INDIV_BEN)
FROM
DONNE.TDEM_PAIMT_ACTE_MD
INNER JOIN DONNE.TSERV_REND_ACTE_MD ON
TSERV_REND_ACTE_MD.NO_INDIV_BEN = TDEM_PAIMT_ACTE_MD.NO_INDIV_BEN AND
TSERV_REND_ACTE_MD.NCI = TDEM_PAIMT_ACTE_MD.NCI AND
TSERV_REND_ACTE_MD.COD_STA_DECIS = TDEM_PAIMT_ACTE_MD.COD_STA_DECIS_DEM_PAIMT
;
SELECT SERV.SMOD_COD_CLA_DISP,
SERV.SMOD_NO_DISP,
SUM(CASE
WHEN (SERV.SMOD_COD_STA_DECIS_DEM_PAIMT = 'PPY')
THEN SERV.SMOD_MNT_DEM_KM
ELSE SERV.SMOD_MNT_PAIMT_KM
END) AS Cout_total_acte
FROM SYSDBA.I_KT_SMOD_MD_KM AS SERV
INNER JOIN P_A1GEII.IDENT_MNT_TOT_REMUN_FIXE AS PROF ON
SERV.SMOD_COD_CLA_DISP = PROF.COD_CLA_DISP AND
SERV.SMOD_NO_DISP = PROF.NO_DISP AND
SERV.SMOD_DAT_KM BETWEEN PROF.Begin_date_EXTRAC AND PROF.End_date_EXTRAC
WHERE ((SERV.SMOD_COD_STA_DECIS_DEM_PAIMT = 'PAY' AND SERV.SMOD_MNT_PAIMT_KM > 0)
OR (SERV.SMOD_COD_STA_DECIS_DEM_PAIMT = 'PPY' AND SERV.SMOD_MNT_DEM_KM > 0))
GROUP BY SERV.SMOD_COD_CLA_DISP,
SERV.SMOD_NO_DISP
;
*** Query completed. No rows found.
*** Total elapsed time was 4 seconds.
Explanation
-------------------------------------------------- | |
| | 5) | We do an all-AMPs RETRIEVE step from P_A1GEII.PROF by way of an all-rows scan with no residual conditions into Spool 3, which
is duplicated on all AMPs. Then we do a SORT to order Spool 3 by row hash. The size of Spool 3 is estimated with high confidence to be 28
rows. The estimated time for this step is 0.03 seconds.
| |
| | 6) | We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an all-rows scan, which is joined to DONNE.TDEM_PAIMT_ACTE_MD by way of
a traversal of index # 4 extracting row ids only. Spool 3 and DONNE.TDEM_PAIMT_ACTE_MD are joined using a nested join, with a join
condition of ("DONNE.TDEM_PAIMT_ACTE_MD.NO_DISP = Spool_3.NO_DISP"). The input table DONNE.TDEM_PAIMT_ACTE_MD will not be cached in memory.
The result goes into Spool 4, which is built locally on the AMPs. Then we do a SORT to order Spool 4 by field Id 1. The size of Spool 4 is
estimated with no confidence to be 5,153 rows. The estimated time for this step is 0.23 seconds.
| |
| | 7) | We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an all-rows scan, which is joined to DONNE.TDEM_PAIMT_ACTE_MD with a
condition of ("((DONNE.TDEM_PAIMT_ACTE_MD.COD_STA_DECIS_DEM_PAIMT = 'PAY') AND (DONNE.TDEM_PAIMT_ACTE_MD.MNT_PAIMT_KM > 0.00 )) OR
((DONNE.TDEM_PAIMT_ACTE_MD.COD_STA_DECIS_DEM_PAIMT = 'PPY') AND (DONNE.TDEM_PAIMT_ACTE_MD.MNT_DEM_KM > 0.00 ))"). Spool 4 and
DONNE.TDEM_PAIMT_ACTE_MD are joined using a row id join, with a join condition of ("(DONNE.TDEM_PAIMT_ACTE_MD.DAT_KM >=
Spool_4.Begin_date_EXTRAC) AND ((DONNE.TDEM_PAIMT_ACTE_MD.DAT_KM <= Spool_4.End_date_EXTRAC) AND ((DONNE.TDEM_PAIMT_ACTE_MD.NO_DISP =
Spool_4.NO_DISP) AND (DONNE.TDEM_PAIMT_ACTE_MD.COD_CLA_DISP = Spool_4.COD_CLA_DISP )))"). The input table DONNE.TDEM_PAIMT_ACTE_MD will not
be cached in memory. The result goes into Spool 5, which is built locally on the AMPs. The size of Spool 5 is estimated with no confidence
to be 5,153 rows. The estimated time for this step is 0.37 seconds.
| |
| | 8) | We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of a RowHash match scan, which is joined to DONNE.TSERV_REND_ACTE_MD.
Spool 5 and DONNE.TSERV_REND_ACTE_MD are joined using a merge join, with a join condition of ("(DONNE.TSERV_REND_ACTE_MD.COD_STA_DECIS =
Spool_5.COD_STA_DECIS_DEM_PAIMT) AND ((DONNE.TSERV_REND_ACTE_MD.NCI = Spool_5.NCI) AND (DONNE.TSERV_REND_ACTE_MD.NO_INDIV_BEN =
Spool_5.NO_INDIV_BEN ))"). The input table DONNE.TSERV_REND_ACTE_MD will not be cached in memory. The result goes into Spool 2, which is
built locally on the AMPs. The size of Spool 2 is estimated with no confidence to be 5,153 rows. The estimated time for this step is 5.65
seconds.
| |
| | 9) | We do a SUM step to aggregate from Spool 2 (Last Use) by way of an all-rows scan, and the grouping identifier in field 1. Aggregate
Intermediate Results are computed globally, then placed in Spool 6. The size of Spool 6 is estimated to be 5,153 rows.
| |
| | 10) | We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of an all-rows scan into Spool 1, which is built locally on the AMPs.
The size of Spool 1 is estimated with low confidence to be 5,153 rows. The estimated time for this step is 0.18 seconds.
| |
| | 11) | 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.
| |
Rudel Simard
R�gie de l'assurance-maladie du Qu�bec
DBA de l'environnement informationnel
| |
