 |
 |
Archives of the TeradataForum
Message Posted: Thu, 05 Oct 2000 @ 14:23:06 GMT
| Subj: | | Where clause VS small table #3 |
| |
| From: | | Rudel Simard |
The end of the explain
----------------------------------------
-- SCRIPT OF STEP 6 --------------------
----------------------------------------
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
INNER JOIN P_A1GEII.IDENT_MNT_TOT_REMUN_FIXE AS PROF ON
TDEM_PAIMT_ACTE_MD.COD_CLA_DISP = PROF.COD_CLA_DISP AND
TDEM_PAIMT_ACTE_MD.NO_DISP = PROF.NO_DISP AND
TDEM_PAIMT_ACTE_MD.DAT_KM BETWEEN PROF.Begin_date_EXTRAC AND PROF.End_date_EXTRAC
WHERE TDEM_PAIMT_ACTE_MD.DAT_KM > 0
;
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
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 6 seconds.
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct P_A1GEII."pseudo table" for read on a RowHash to prevent global deadlock for P_A1GEII.PROF.
| |
| | 2) | Next, we lock a distinct DONNE."pseudo table" for read on a RowHash to prevent global deadlock for DONNE.TSERV_REND_ACTE_MD.
| |
| | 3) | We lock a distinct DONNE."pseudo table" for read on a RowHash to prevent global deadlock for DONNE.TDEM_PAIMT_ACTE_MD.
| |
| | 4) | We lock P_A1GEII.PROF for read, we lock DONNE.TSERV_REND_ACTE_MD for read, and we lock DONNE.TDEM_PAIMT_ACTE_MD for read.
| |
| | 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 low confidence to be 3 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.DAT_KM > 0) AND (((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 low confidence to be 3 rows. The estimated time for this step is 0.36
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.NO_INDIV_BEN =
Spool_5.NO_INDIV_BEN) AND ((DONNE.TSERV_REND_ACTE_MD.NCI = Spool_5.NCI) AND (DONNE.TSERV_REND_ACTE_MD.COD_STA_DECIS =
Spool_5.COD_STA_DECIS_DEM_PAIMT ))"). 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 low confidence to be 3 rows. The estimated time for this step is
0.20 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 3 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 3 rows. The estimated time for this step is 0.17 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
| |
