Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 15 Jun 2001 @ 17:17:37 GMT


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


Subj:   Why redistributing?
 
From:   Rudel Simard

Hi everybody,

I create a view with a derive table, and I try to join the derive table (about 55 000 000 records) with a small table (6 records). The system redistributes the big tables!!!!

Why not copying on each amp the small table. Does anybody have a idea how the system work in that case.

I give to you the explain. The explain is well until step 9.

Thank you for your help.

Rudel Simard
Régie de l'assurance-maladie du Québec
DBA de l'environnement informationnel

REPLACE VIEW U8724.I_CAA110_SMED AS

SELECT
TD_CA_SMED_DEM_AJUS.AN_CIVIL_RETN (NAMED AMED_AN_CIVIL_RETN),
SUM(TD_CA_SMED_DEM_AJUS.MNT_AUTOR_MED) (NAMED AMED_MNT_AUTOR_MED) ,
COUNT(*)(NAMED AMED_TOTAL_TEST)

FROM (SELECT TSERV.AN_CIVIL_RETN
            ,TSERV.MNT_AUTOR_MED
            ,TSERV.COD_CATG_LISTE_MED
      FROM   PROD.D_CA_SMED_DEM_AJUS TSERV
      WHERE  TSERV.COD_STA_DECIS = 'PAY'
      GROUP  BY TSERV.NO_RAMQ_DEM_MED
      QUALIFY RANK(TSERV.DAT_DECIS_AJUS_CONTB DESC) = 1)
      AS
      TD_CA_SMED_DEM_AJUS(AN_CIVIL_RETN,MNT_AUTOR_MED,COD_CATG_LISTE_MED)

INNER JOIN DONNE_MAJ.TCA_SELEC_LISTE_MED ON
TD_CA_SMED_DEM_AJUS.COD_CATG_LISTE_MED =
TCA_SELEC_LISTE_MED.COD_CATG_LISTE_MED

GROUP BY TD_CA_SMED_DEM_AJUS.AN_CIVIL_RETN
;

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct DONNE_MAJ."pseudo table" for read on a RowHash to prevent global deadlock for DONNE_MAJ.TCA_SELEC_LISTE_MED.  
  2)Next, we lock a distinct DONNE_MAJ."pseudo table" for read on a RowHash to prevent global deadlock for DONNE_MAJ.TCA_AN_TRAIT_STATS_MED.  
  3)We lock a distinct DONNE."pseudo table" for read on a RowHash to prevent global deadlock for DONNE.TAJUS_CONTB_BEN_MED.  
  4)We lock a distinct DONNE."pseudo table" for read on a RowHash to prevent global deadlock for DONNE.TDEM_PAIMT_MED.  
  5)We lock DONNE_MAJ.TCA_SELEC_LISTE_MED for read, we lock DONNE_MAJ.TCA_AN_TRAIT_STATS_MED for read, we lock DONNE.TAJUS_CONTB_BEN_MED for read, and we lock DONNE.TDEM_PAIMT_MED for read.  
  6)We do an all-AMPs RETRIEVE step from DONNE_MAJ.TCA_AN_TRAIT_STATS_MED by way of an all-rows scan with no residual conditions into Spool 3, which is duplicated on all AMPs. The size of Spool 3 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 3 (Last Use) by way of an all-rows scan, which is joined to DONNE.TDEM_PAIMT_MED with a condition of ("DONNE.TDEM_PAIMT_MED.COD_STA_DECIS = 'PAY'"). Spool 3 and DONNE.TDEM_PAIMT_MED are joined using a product join, with a join condition of ("(DONNE.TDEM_PAIMT_MED.DAT_SERV >= Spool_3.DD_TRAIT_STATS_MED) AND (DONNE.TDEM_PAIMT_MED.DAT_SERV <= Spool_3.DF_TRAIT_STATS_MED)"). The input table DONNE.TDEM_PAIMT_MED will not be cached in memory, but it is eligible for synchronized scanning. The result goes into Spool 4, which is built locally on the AMPs. Then we do a SORT to order Spool 4 by row hash. The result spool file will not be cached in memory. The size of Spool 4 is estimated with high confidence to be 62,197,384 rows. The estimated time for this step is 2 hours and 11 minutes.  
  8)We do an all-AMPs JOIN step from DONNE.TAJUS_CONTB_BEN_MED by way of a RowHash match scan with no residual conditions, which is joined to Spool 4 (Last Use). DONNE.TAJUS_CONTB_BEN_MED and Spool 4 are right outer joined using a merge join, with a join condition of ("(Spool_4.NO_INDIV_BEN = DONNE.TAJUS_CONTB_BEN_MED.NO_INDIV_BEN) AND (((Spool_4.NO_RAMQ_DEM_MED_REVIS = DONNE.TAJUS_CONTB_BEN_MED.NO_RAMQ_DEM_MED) OR (Spool_4.NO_RAMQ_DEM_MED_REVIS IS NULL )) AND ((Spool_4.NO_RAMQ_DEM_MED = DONNE.TAJUS_CONTB_BEN_MED.NO_RAMQ_DEM_MED) OR (Spool_4.NO_RAMQ_DEM_MED_REVIS = DONNE.TAJUS_CONTB_BEN_MED.NO_RAMQ_DEM_MED )))"). The result goes into Spool 2, which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 2 is estimated with low confidence to be 85,562,432 rows. The estimated time for this step is 14 minutes and 48 seconds.  
  9)We do an all-AMPs STAT FUNCTION step from Spool 2 (Last Use) by way of an all-rows scan into Spool 8 (Last Use), which is redistributed by hash code to all AMPs. The result rows are put into Spool 1, which is built locally on the AMPs.  
  10)We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan into Spool 13, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 13 by row hash. The size of Spool 13 is estimated with no confidence to be 0 row. The estimated time for this step is 0.04 seconds.  
  11)We do an all-AMPs JOIN step from Spool 13 (Last Use) by way of a RowHash match scan, which is joined to DONNE_MAJ.TCA_SELEC_LISTE_MED. Spool 13 and DONNE_MAJ.TCA_SELEC_LISTE_MED are joined using a merge join, with a join condition of ("Spool_13.COD_CATG_LISTE_MED = DONNE_MAJ.TCA_SELEC_LISTE_MED.COD_CATG_LISTE_MED"). The result goes into Spool 12, which is built locally on the AMPs. The size of Spool 12 is estimated with no confidence to be 0 row. The estimated time for this step is 0.17 seconds.  
  12)We do a SUM step to aggregate from Spool 12 (Last Use) by way of an all-rows scan, and the grouping identifier in field 2. Aggregate Intermediate Results are computed globally, then placed in Spool 14. The size of Spool 14 is estimated to be 0 row.  
  13)We do an all-AMPs RETRIEVE step from Spool 14 (Last Use) by way of an all-rows scan into Spool 11, which is built locally on the AMPs. The size of Spool 11 is estimated with no confidence to be 0 row. The estimated time for this step is 0.17 seconds.  
  14)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The contents of Spool 11 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