Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 05 Oct 2000 @ 14:23:06 GMT


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


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



     
  <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