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>>

Attachments

Library

Quick Reference