Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Where clause VS small table #1
 
From:   Rudel Simard

Hi,

I have a non-understanding of a way teradata work, does anyboby want take time to help me?

I explain step by step how the request is contructed :

1. It's a view with access to 2 tables with a WHERE clause in it.

     View named I_KT_SMOD_MD_KM:

     Tables  : TDEM_PAIMT_ACTE_MD  et TSERV_REND_ACTE_MD

     where clause : WHERE TDEM_PAIMT_ACTE_MD.DAT_KM > 0

2. We use the view created in step 1 with this WHERE clause :

     WHERE SERV.SMOD_COD_CLA_DISP = '1'
     AND SERV.SMOD_NO_DISP      = 78001
     AND SERV.SMOD_DAT_KM BETWEEN '19990101' (DATE, FORMAT 'YYYYMMDD')
                              AND '19991231' (DATE, FORMAT 'YYYYMMDD')
     AND ((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))

It takes 9 seconds. But it's not really the way we want to use it. We want the user insert his criteria in a small table. That's why we do the next step.


3. We create a table with some criteria of the request of step 2.

     View named P_A1GEII.IDENT_MNT_TOT_REMUN_FIXE:

     The table contains  COD_CLA_DISP (1), NO_DISP (78001),
     Begin_Date_EXTRAC (19990101) and End_Date_EXTRAC (19991231).

     We do COLLECT STATISTICS on columns.

4. We re-execute the request using the view (step 1) and the small table (step 3)

It takes 3 minutes 30 seconds (!!!!!!!!!!).

     Joint condition:  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 clause :  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))

5. If I remove the WHERE clause from the view (step 1) and I re-execute the request 4, it takes The result is not good but it takes 4 secondes au lieu de 3 minutes 30 seconds.

WHY????

6. If I keep the WHERE clause in the view and add, in the view, the small table created in step 3 It takes 6 seconds instead of 3 minutes and 30 secods.

WHY?????

Here the definition of each step and the explain.

     ----------------------------------------
     -- SCRIPT OF STEP 1 --------------------
     ----------------------------------------

     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

     WHERE TDEM_PAIMT_ACTE_MD.DAT_KM > 0
     ;

     ----------------------------------------
     -- SCRIPT OF STEP 2 --------------------
     ----------------------------------------

     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_CLA_DISP = '1'
       AND SERV.SMOD_NO_DISP      = 78001
       AND SERV.SMOD_DAT_KM BETWEEN '19990101' (DATE, FORMAT 'YYYYMMDD')
                                AND '19991231' (DATE, FORMAT 'YYYYMMDD')
       AND ((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. 1 rows found.
      *** Total elapsed time was 9 seconds.

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct DONNE."pseudo table" for read on a RowHash to prevent global deadlock for DONNE.TSERV_REND_ACTE_MD.  
  2)Next, we lock a distinct DONNE."pseudo table" for read on a RowHash to prevent global deadlock for DONNE.TDEM_PAIMT_ACTE_MD.  
  3)We lock DONNE.TSERV_REND_ACTE_MD for read, and we lock DONNE.TDEM_PAIMT_ACTE_MD for read.  
  4)We do an all-AMPs JOIN step from DONNE.TDEM_PAIMT_ACTE_MD by way of index # 4 "DONNE.TDEM_PAIMT_ACTE_MD.NO_DISP = 78001" with a residual condition of ("(DONNE.TDEM_PAIMT_ACTE_MD.COD_CLA_DISP = '1') AND ((DONNE.TDEM_PAIMT_ACTE_MD.DAT_KM <= DATE '1999-12-31') AND ((DONNE.TDEM_PAIMT_ACTE_MD.DAT_KM >= DATE '1999-01-01') 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 ))) AND (DONNE.TDEM_PAIMT_ACTE_MD.DAT_KM > 0 ))))"), which is joined to DONNE.TSERV_REND_ACTE_MD. DONNE.TDEM_PAIMT_ACTE_MD 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 = DONNE.TDEM_PAIMT_ACTE_MD.COD_STA_DECIS_DEM_PAIMT) AND ((DONNE.TDEM_PAIMT_ACTE_MD.NCI = DONNE.TSERV_REND_ACTE_MD.NCI) AND (DONNE.TDEM_PAIMT_ACTE_MD.NO_INDIV_BEN = DONNE.TSERV_REND_ACTE_MD.NO_INDIV_BEN ))"). The input tables DONNE.TDEM_PAIMT_ACTE_MD and DONNE.TSERV_REND_ACTE_MD will not be cached in memory, but DONNE.TDEM_PAIMT_ACTE_MD is eligible for synchronized scanning. 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 15,452 rows. The estimated time for this step is 32.81 seconds.  
  5)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 3. The size of Spool 3 is estimated to be 15,452 rows.  
  6)We do an all-AMPs RETRIEVE step from Spool 3 (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 15,452 rows. The estimated time for this step is 0.21 seconds.  
  7)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.  


     ----------------------------------------
     -- SCRIPT OF STEP 3 --------------------
     ----------------------------------------

     SHOW TABLE P_A1GEII.IDENT_MNT_TOT_REMUN_FIXE;

     CREATE MULTISET TABLE P_A1GEII.IDENT_MNT_TOT_REMUN_FIXE ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           COD_CLA_DISP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
           NO_DISP INTEGER,
           PRENOM CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
           NOM CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           ADR_LIGNE_2 CHAR(45) CHARACTER SET LATIN NOT CASESPECIFIC,
           ADR_LIGNE_3 CHAR(45) CHARACTER SET LATIN NOT CASESPECIFIC,
           PROVINCE CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           COD_POSTL CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC,
           COD_SPEC INTEGER,
           NOM_SPEC CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
           Begin_date_EXTRAC DATE FORMAT 'YY/MM/DD',
           End_date_EXTRAC DATE FORMAT 'YY/MM/DD',
           NBR_SPEC_DISP SMALLINT)
     PRIMARY INDEX ( NO_DISP )
     INDEX ( COD_CLA_DISP ,NO_DISP )
     INDEX ( COD_SPEC )
     INDEX ( Begin_date_EXTRAC )
     INDEX ( End_date_EXTRAC );

     SELECT COD_CLA_DISP
           ,NO_DISP
           ,Begin_date_EXTRAC (FORMAT 'YYYY-MM-DD')
           ,End_date_EXTRAC (FORMAT 'YYYY-MM-DD')
     FROM  P_A1GEII.IDENT_MNT_TOT_REMUN_FIXE
     ;

      *** Query completed. One row found. 13 columns returned.
      *** Total elapsed time was 1 second.

     COD_CLA_DISP     NO_DISP Begin_date_EXTRAC End_date_EXTRAC
     ------------ ----------- ------------- -------------
     1                  78001    1999-01-01    1999-12-31

     HELP STATISTICS P_A1GEII.IDENT_MNT_TOT_REMUN_FIXE;

     Date      Time      Unique Values   Column Names
     ------------------------------------------------------------------------
     00/09/26 14:59:21               1   COD_CLA_DISP
     00/09/26 15:23:29               1   NO_DISP
     00/09/26 15:23:31               1   COD_SPEC
     00/09/26 15:23:32               1   Begin_date_EXTRAC
     00/09/26 15:23:33               1   End_date_EXTRAC
     00/09/26 15:23:30               1   COD_CLA_DISP,NO_DISP

     Folowing in next message...........

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