 |
 |
Archives of the TeradataForum
Message Posted: Tue, 21 Feb 2012 @ 21:12:25 GMT
| Subj: | | Duplicate to all amps |
| |
| From: | | filipsil |
How Can I avoid the duplication data on all AMPs, because, this situation is destroying the performance of my query. I tried different
modifications, but none solved.
Following the execution plan :
This query is optimized using type 2 profile DR146709, profileid 10001.
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock DWP_TDM.TB_PESSOA in view DWP_WDM.VW_TB_PESSOA_NF for access, we lock DWP_TDM.TB_LINHA_NF_CR in view DWP_VTDM.TB_LINHA_NF_CR
for access, we lock DWP_TDM.TB_INST_PROD_EQUIP_LINHA_NF_CR in view DWP_VTDM.TB_INST_PROD_EQUIP_LINHA_NF_CR for access, we lock
DWP_TDM.TB_INST_PROD_EQUIP in view DWP_VTDM.TB_INST_PROD_EQUIP for access, we lock DWP_TDM.TB_PESSOA_NF in view DWP_WDM.VW_TB_PESSOA_NF for
access, and we lock DWP_TDM.TB_NF in view DWP_VTDM.TB_NF for access.
| |
| | 2) | Next, we do an all-AMPs JOIN step from DWP_TDM.TB_NF in view DWP_VTDM.TB_NF by way of a RowHash match scan with a condition of
("(DWP_TDM.TB_NF in view DWP_VTDM.TB_NF.NU_TIPO_NF_CR = 1) AND ((((TRANSLATE(((( CASE WHEN (TRIM(BOTH FROM DWP_TDM.TB_NF in view
DWP_VTDM.TB_NF.DT_NF (VARCHAR(64), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT 'YYYY-MM-DD'))(VARCHAR(64), CHARACTER SET LATIN, NOT
CASESPECIFIC)IS NULL) THEN ('') ELSE (TRIM(BOTH FROM DWP_TDM.TB_NF in view DWP_VTDM.TB_NF.DT_NF (VARCHAR(64), CHARACTER SET LATIN, NOT
CASESPECIFIC, FORMAT 'YYYY-MM-DD'))(VARCHAR(64), CHARACTER SET LATIN, NOT CASESPECIFIC)) END ))||' 00:00:00')USING
LATIN_TO_UNICODE))(TIMESTAMP(6)))<= TIMESTAMP '2012-02-13 00:00:00.000000') AND (NOT (DWP_TDM.TB_NF in view DWP_VTDM.TB_NF.NU_NF IS NULL )))"),
which is joined to DWP_TDM.TB_PESSOA_NF in view DWP_WDM.VW_TB_PESSOA_NF by way of a RowHash match scan with a condition of ("DWP_TDM.TB_PESSOA_NF
in view DWP_WDM.VW_TB_PESSOA_NF.NU_TIPO_PAPEL_PESSOA_NF = 2"). DWP_TDM.TB_NF and DWP_TDM.TB_PESSOA_NF are left outer joined using a merge join,
with a join condition of ("DWP_TDM.TB_NF.NU_NF = DWP_TDM.TB_PESSOA_NF.NU_NF"). The result goes into Spool 9 (all_amps) (compressed columns
allowed), which is built locally on the AMPs. The size of Spool 9 is estimated with no confidence to be 962,466 rows (67,372,620 bytes). The
estimated time for this step is 0.21 seconds.
| |
| | 3) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of a RowHash match scan, which is joined to DWP_TDM.TB_LINHA_NF_CR in view
DWP_VTDM.TB_LINHA_NF_CR by way of a RowHash match scan with no residual conditions. Spool 9 and DWP_TDM.TB_LINHA_NF_CR are left outer joined
using a merge join, with a join condition of ("NU_NF = DWP_TDM.TB_LINHA_NF_CR.NU_NF_CR"). The result goes into Spool 10 (all_amps) (compressed
columns allowed), which is redistributed by the hash code of (DWP_TDM.TB_LINHA_NF_CR.NU_NF_CR) to all AMPs. Then we do a SORT to order Spool 10
by row hash. The size of Spool 10 is estimated with no confidence to be 1,762,150 rows (230,841,650 bytes). The estimated time for this step is
0.50 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from DWP_TDM.TB_PESSOA in view DWP_WDM.VW_TB_PESSOA_NF by way of an all-rows scan with no residual
conditions into Spool 11 (all_amps) (compressed columns allowed), which is duplicated on all AMPs. Then we do a SORT to order Spool 11 by the
hash code of (DWP_TDM.TB_PESSOA.NU_PESSOA). The result spool file will not be cached in memory. The size of Spool 11 is estimated with high
confidence to be 73,647,616,954 rows (1,988,485,657,758 bytes). The estimated time for this step is 1 hour and 49 minutes.
| | |
| | 4) | We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of a RowHash match scan, which is joined to
DWP_TDM.TB_INST_PROD_EQUIP_LINHA_NF_CR in view DWP_VTDM.TB_INST_PROD_EQUIP_LINHA_NF_CR by way of a RowHash match can. Spool 10 and
DWP_TDM.TB_INST_PROD_EQUIP_LINHA_NF_CR are eft outer joined using a merge join, with a join condition of ("(NU_NF_CR =
DWP_TDM.TB_INST_PROD_EQUIP_LINHA_NF_CR.NU_NF_CR) AND (NU_LINHA_NF_CR = DWP_TDM.TB_INST_PROD_EQUIP_LINHA_NF_CR.NU_LINHA_NF_CR)"). The result goes
into Spool 12 (all_amps) (compressed columns allowed), which is built locally on the AMPs. Then we do a SORT to order Spool 12 by the hash code
of (DWP_TDM.TB_PESSOA_NF.NU_PESSOA_NF). The size of Spool 12 is estimated with no confidence to be 98,717,772 rows (13,326,899,220 bytes). The
estimated time for this step is 16.30 seconds.
| |
| | 5) | We do an all-AMPs JOIN step from Spool 11 (Last Use) by way of a RowHash match scan, which is joined to Spool 12 (Last Use) by way of a
RowHash match scan. Spool 11 and Spool 12 are right outer joined using a merge join, with a join condition of ("NU_PESSOA_NF = NU_PESSOA"). The
result goes into Spool 13 (all_amps) (compressed columns allowed), which is redistributed by the hash code of
(DWP_TDM.TB_INST_PROD_EQUIP_LINHA_NF_CR.NU_INST_PROD_EQUIP) to all AMPs. Then we do a SORT to order Spool 13 by row hash. The result spool file
will not be cached in memory. The size of Spool 13 is estimated with no confidence to be 98,717,772 rows (14,708,948,028 bytes). The estimated
time for this step is 2 minutes and 12 seconds.
| |
| | 6) | We do an all-AMPs JOIN step from Spool 13 (Last Use) by way of a RowHash match scan, which is joined to DWP_TDM.TB_INST_PROD_EQUIP in view
DWP_VTDM.TB_INST_PROD_EQUIP by way of a RowHash match scan with no residual conditions. Spool 13 and DWP_TDM.TB_INST_PROD_EQUIP are left outer
joined using a merge join, with a join condition of ("NU_INST_PROD_EQUIP = DWP_TDM.TB_INST_PROD_EQUIP.NU_INST_PROD_EQUIP"). The input table
DWP_TDM.TB_INST_PROD_EQUIP will not be cached in memory. The result goes into Spool 8 (group_amps), which is built locally on the AMPs. The
result spool file will not be cached in memory. The size of Spool 8 is estimated with no confidence to be 98,717,772 rows (21,816,627,612 bytes).
The estimated time for this step is 11.57 seconds.
| |
| | 7) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 8 are sent back to the user as the result of statement 1. The total estimated time is 1 hour and 51 minutes.
| |
att,
Filipe da Silva
IBM Brasil - GBS / AS
| |
