Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 05 Oct 2000 @ 14:22:32 GMT


     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 


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



     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016