 |
 |
Archives of the TeradataForum
Message Posted: Fri, 15 Jun 2001 @ 17:17:37 GMT
| 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
| |
