InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comWed, 03 Dec 2008 16:55:03 GMT20http://www.teradata.com/teradataforum/Topic11776-9-1.aspxgrouping identifierin field 1 ex--sel * from a group by filed1;filed1 is column in ur query used for grouping or even ordering.Mon, 16 Jun 2008 03:36:48 GMTprakharhttp://www.teradata.com/teradataforum/Topic11776-9-1.aspx5) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by way of an all-rows scan, and the Aggregate Intermediate Results are computed globally, then placed in Spool 8. The size of Spool 8 is estimated with index join confidence to be 839,862 rows. The estimated time for this step is 0.24 seconds.GROUPING IDENTIFIED IN FIELD 1 CAN ANY BODY REPLY ?Sun, 15 Jun 2008 02:32:14 GMTthelittlechamphttp://www.teradata.com/teradataforum/Topic11776-9-1.aspxThis comes in the explain plan : 5) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by way of an all-rows scan, Aggregate Intermediate Results are computed globally, then placed in Spool 8. The size of Spool 8 is estimated with index join confidence to be 839,862 rows. The estimated time for this step is 0.24 seconds.can anybody explain grouping identifier in field 1 ?What it is referring to ?Sun, 15 Jun 2008 02:30:17 GMTthelittlechamphttp://www.teradata.com/teradataforum/Topic11776-9-1.aspxCan anybody explain grouping identifier in field 1 ?Wht does this mean ? Wht field or column it refers to ?Sun, 15 Jun 2008 02:26:52 GMTthelittlechamphttp://www.teradata.com/teradataforum/Topic11776-9-1.aspxExplanation 1) First, we lock ProdBBYdb.TBEND_SA_PYMT for access, we lock PRODBBYDB.TBEND_BU_FISC_DT for access, we lock PRODBBYDB.TBEND_FI_FIN_PLAN for access, we lock PRODBBYDB.TBEND_FI_TNDR_TYP_XREF for access, and we lock PRODETLSTAGE.DTL for access. 2) Next, we do an all-AMPs RETRIEVE step from 52 partitions of ProdBBYdb.TBEND_SA_PYMT with a condition of ( "(ProdBBYdb.TBEND_SA_PYMT.SLS_BSNS_DT <= DATE '2008-05-27') AND (ProdBBYdb.TBEND_SA_PYMT.SLS_BSNS_DT >= DATE '2008-04-06')") into Spool 8 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 8 by row hash. The size of Spool 8 is estimated with high confidence to be 46,707,403 rows. The estimated time for this step is 10.76 seconds. 3) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of a RowHash match scan, which is joined to PRODETLSTAGE.DTL by way of a RowHash match scan with no residual conditions. Spool 8 and PRODETLSTAGE.DTL are joined using a merge join, with a join condition of ("SLS_KEY = PRODETLSTAGE.DTL.SLS_KEY"). The input table PRODETLSTAGE.DTL will not be cached in memory. The result goes into Spool 7 (all_amps), which is built locally on the AMPs. The size of Spool 7 is estimated with low confidence to be 46,977,045 rows. The estimated time for this step is 8.14 seconds. 4) We execute the following steps in parallel. 1) We do an all-AMPs SUM step to aggregate from Spool 7 (Last Use) by way of an all-rows scan, and the grouping identifier in field 1. Aggregate Intermediate Results are computed locally, then placed in Spool 9. The size of Spool 9 is estimated with no confidence to be 35,232,784 rows. The estimated time for this step is 34.04 seconds. 2) We do an all-AMPs RETRIEVE step from PRODBBYDB.TBEND_FI_TNDR_TYP_XREF by way of an all-rows scan with a condition of ("(NOT (PRODBBYDB.TBEND_FI_TNDR_TYP_XREF.TNDR_TYP_CDE IS NULL )) AND (NOT (PRODBBYDB.TBEND_FI_TNDR_TYP_XREF.TNDR_SUBTYP_CDE IS NULL ))") into Spool 11 (all_amps), which is duplicated on all AMPs. The size of Spool 11 is estimated with high confidence to be 14,400 rows. The estimated time for this step is 0.04 seconds. 5) We do an all-AMPs JOIN step from PRODBBYDB.TBEND_BU_FISC_DT by way of an all-rows scan with a condition of ( "(PRODBBYDB.TBEND_BU_FISC_DT.CALNDR_DT <= DATE '2008-05-27') AND (PRODBBYDB.TBEND_BU_FISC_DT.CALNDR_DT >= DATE '2008-04-06')"), which is joined to Spool 11 (Last Use) by way of an all-rows scan. PRODBBYDB.TBEND_BU_FISC_DT and Spool 11 are joined using a product join, [color=#FF0000]with a join condition of ("(1=1)").[/color] The result goes into Spool 12 (all_amps), which is duplicated on all AMPs. Then we do a SORT to partition by rowkey. The size of Spool 12 is estimated with low confidence to be 748,800 rows. The estimated time for this step is 0.29 seconds. 6) We execute the following steps in parallel. 1) We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of an all-rows scan, which is joined to 52 partitions of ProdBBYdb.TBEND_SA_PYMT with a condition of ( "(ProdBBYdb.TBEND_SA_PYMT.SLS_BSNS_DT >= DATE '2008-04-06') AND (ProdBBYdb.TBEND_SA_PYMT.SLS_BSNS_DT <= DATE '2008-05-27')"). Spool 12 and ProdBBYdb.TBEND_SA_PYMT are [color=#FF0000]joined using a product join,[/color] with a join condition of ( "(TNDR_SUBTYP_CDE = ProdBBYdb.TBEND_SA_PYMT.TNDR_SUBTYP_CDE) AND ((TNDR_TYP_CDE = ProdBBYdb.TBEND_SA_PYMT.TNDR_TYP_CDE) AND (CALNDR_DT = ProdBBYdb.TBEND_SA_PYMT.SLS_BSNS_DT ))") enhanced by dynamic partition elimination. The input table ProdBBYdb.TBEND_SA_PYMT will not be cached in memory, but it is eligible for synchronized scanning. The result goes into Spool 13 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 13 by row hash. The size of Spool 13 is estimated with low confidence to be 2,136,010 rows. The estimated time for this step is 5.58 seconds. 2) We do an all-AMPs RETRIEVE step from Spool 9 (Last Use) by way of an all-rows scan into Spool 14 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 14 by row hash. The size of Spool 14 is estimated with no confidence to be 35,232,784 rows. The estimated time for this step is 16.13 seconds. 7) We execute the following steps in parallel. 1) We do an all-AMPs JOIN step from Spool 13 (Last Use) by way of an all-rows scan, which is joined to Spool 14 (Last Use) by way of an all-rows scan. Spool 13 and Spool 14 are joined using an inclusion merge join, with a join condition of ( "(Field_4 = Field_4) AND ((SLS_KEY = SLS_KEY) AND (SLS_BSNS_DT = SLS_BSNS_DT ))"). The result goes into Spool 15 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 15 by row hash. The size of Spool 15 is estimated with index join confidence to be 2,136,010 rows. The estimated time for this step is 2.05 seconds. 2) We do an all-AMPs RETRIEVE step from PRODBBYDB.TBEND_FI_FIN_PLAN by way of an all-rows scan with no residual conditions into Spool 16 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 16 by row hash. The size of Spool 16 is estimated with low confidence to be 5,280 rows. The estimated time for this step is 0.01 seconds. 8) We do an all-AMPs JOIN step from Spool 15 (Last Use) by way of a RowHash match scan, which is joined to Spool 16 (Last Use) by way of a RowHash match scan. Spool 15 and Spool 16 are left outer joined using a merge join, with a join condition of ( "(FISC_YR_NBR = FISC_YR_NBR) AND ((FISC_MTH_NBR = FISC_MTH_NBR) AND ((TNDR_ID = TNDR_ID) AND ((TRANSLATE((( CASE WHEN (NOT (PYMT_FIN_CDE IS NULL )) THEN (PYMT_FIN_CDE) ELSE (' 0') END ))USING LATIN_TO_UNICODE)(FLOAT, FORMAT '-9.99999999999999E-999'))= (( CASE WHEN (NOT (PYMT_FIN_PLAN_NBR IS NULL )) THEN (PYMT_FIN_PLAN_NBR) ELSE (0.) END) ))))"). The result goes into Spool 4 (all_amps), which is built locally on the AMPs. The size of Spool 4 is estimated with low confidence to be 9,146,506 rows. The estimated time for this step is 0.71 seconds. 9) We do an all-AMPs SUM step to aggregate from Spool 4 (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 17. The size of Spool 17 is estimated with no confidence to be 6,859,880 rows. The estimated time for this step is 4.09 seconds. 10) We execute the following steps in parallel. 1) We do an all-AMPs RETRIEVE step from Spool 17 (Last Use) by way of an all-rows scan into Spool 2 (all_amps), which is built locally on the AMPs. The size of Spool 2 is estimated with no confidence to be 6,859,880 rows. The estimated time for this step is 0.69 seconds. 2) We do an all-AMPs SUM step to aggregate from PRODETLSTAGE.TBENW_SA_MTRX_DLY_DTL by way of an all-rows scan with no residual conditions, and the grouping identifier in field 1 locking for access. Aggregate Intermediate Results are computed locally, then placed in Spool 20. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 20 is estimated with high confidence to be 19,267,864 rows. The estimated time for this step is 19.42 seconds. 11) We execute the following steps in parallel. 1) We do an all-AMPs RETRIEVE step from Spool 20 (Last Use) by way of an all-rows scan into Spool 1 (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 19,267,864 rows. The estimated time for this step is 1.65 seconds. 2) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of an all-rows scan into Spool 23 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 23 by row hash. The size of Spool 23 is estimated with no confidence to be 6,859,880 rows. The estimated time for this step is 1.72 seconds. 12) We execute the following steps in parallel. 1) We do an all-AMPs JOIN step from Spool 23 (Last Use) by way of a RowHash match scan, which is joined to PRODETLSTAGE.SLSGRP by way of a RowHash match scan with a condition of ( "PRODETLSTAGE.SLSGRP.GROUP_SRNO = 1") locking PRODETLSTAGE.SLSGRP for access. Spool 23 and PRODETLSTAGE.SLSGRP are joined using a merge join, with a join condition of ("PRODETLSTAGE.SLSGRP.SLS_KEY = SLS_KEY"). The input table PRODETLSTAGE.SLSGRP will not be cached in memory. The result goes into Spool 24 (all_amps), which is built locally on the AMPs. The size of Spool 24 is estimated with no confidence to be 6,859,880 rows. The estimated time for this step is 5.35 seconds. 2) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan into Spool 25 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 25 by row hash. The size of Spool 25 is estimated with high confidence to be 19,267,864 rows. The estimated time for this step is 4.58 seconds. 13) We do an all-AMPs JOIN step from Spool 24 (Last Use) by way of a RowHash match scan, which is joined to Spool 25 (Last Use) by way of a RowHash match scan. Spool 24 and Spool 25 are joined using a merge join, with a join condition of ("(SLS_KEY = SLS_KEY) AND (SLS_KEY = SLS_KEY)"). The result goes into Spool 22 (group_amps), which is built locally on the AMPs. The size of Spool 22 is estimated with no confidence to be 69,933,876 rows. The estimated time for this step is 5.93 seconds. 14) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> The contents of Spool 22 are sent back to the user as the result of statement 1. The total estimated time is 1 minute and 49 seconds. The first product join is clear as it is a small table join n need to improe performance Can anybody clarify for the othr product join it is b/w spool and a large table ?How cud it be improved ?Mon, 09 Jun 2008 15:14:00 GMTthelittlechamp