InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comWed, 03 Dec 2008 20:08:02 GMT20http://www.teradata.com/teradataforum/Topic10860-1-1.aspxtry to cheat the optimizer using left join and push the rightside col is not null in the where clause to mimic a inner join. Using this approach you can "pin-down" the join order and try to eliminate as any rows as possible at the early stage. Optimizer sometimes cannot determine the best join order since the permutation of N table innerjion is N!, which can easily be a very large number. However, left join is not commutative, therefore, you can "outsmart" optimizer. For example:sel * from a inner join b inner join c inner join d ... inner join Kwhere is the same as sel k left jion a inner jion b inner jion c... inner join jwhere and a.col is not nullThu, 27 Mar 2008 09:57:54 GMTemilwuhttp://www.teradata.com/teradataforum/Topic10860-1-1.aspxTry the below,INSERT INTO MARC_MCGUCKIAN.Rec_Clm_Est_WH_CT1 (policy_id, policy_desc, claim_ver_id, claim_code_desc, region, currency, product_type,estrec_code, riskno, match_est, match_recest, pay_est, rec_est) SELECT a15.policy_id, a15.policy_desc, a12.claim_ref_id, transfer_claim_ref, a15.Po_corp_unit_id, currency_id, SUBSTR(a13.Pr_type_id,2,3), Ca_est_rec_id, TRIM(SUBSTR(Claim_line_desc,23,2)), 'N', 'N', CAST(SUM(CAST(F_CL_EST_PAY AS NUMERIC(16,2))) AS NUMERIC(16,2)), CAST(SUM(CAST(F_CL_EST_RECOVER AS NUMERIC(16,2))) AS NUMERIC(16,2))FROM MARC_MCGUCKIAN.FAT_BSE_CL_PAY_CRE_TRANS1 a11 JOIN prt_lu_product a13 ON (a11.Product_id = a13.Product_id)JOIN cav_lu_claim_reference a12 ON (a11.claim_ref_id = a12.claim_ref_id)JOIN POt_lu_policy a15 ON (a11.Policy_id = a15.Policy_id)WHERE CT_TYPE_ID <> '90'GROUP BY a12.claim_ref_id, Ca_est_rec_id, transfer_claim_ref, a15.Po_corp_unit_id, currency_id, TRIM(SUBSTR(Claim_line_desc,23,2)), a15.policy_id, a15.policy_desc, SUBSTR(a13.Pr_type_id,2,3);I have rearranged the join table a13 ahead of a12 as a13 has fewer no of records to join with the left table.Not sure this will be effective, but u can check if this works :-)Thu, 27 Mar 2008 07:28:21 GMTsakthiganeshthttp://www.teradata.com/teradataforum/Topic10860-1-1.aspxHi,Can you provide a li'l more info. Can you provide the table structures so see the primary indexes. And also highlight which of these tables are "look-up" tables?Wed, 26 Mar 2008 09:09:09 GMTTD_Archhttp://www.teradata.com/teradataforum/Topic10860-1-1.aspxI am trying to reduce this Query's runtime by 50% or more!I've tried a number of things...INSERT INTO MARC_MCGUCKIAN.Rec_Clm_Est_WH_CT1 (policy_id, policy_desc, claim_ver_id, claim_code_desc, region, currency, product_type,estrec_code, riskno, match_est, match_recest, pay_est, rec_est) SELECT a15.policy_id, a15.policy_desc, a12.claim_ref_id, transfer_claim_ref, a15.Po_corp_unit_id, currency_id, SUBSTR(a13.Pr_type_id,2,3), Ca_est_rec_id, TRIM(SUBSTR(Claim_line_desc,23,2)), 'N', 'N', CAST(SUM(CAST(F_CL_EST_PAY AS NUMERIC(16,2))) AS NUMERIC(16,2)), CAST(SUM(CAST(F_CL_EST_RECOVER AS NUMERIC(16,2))) AS NUMERIC(16,2))FROM MARC_MCGUCKIAN.FAT_BSE_CL_PAY_CRE_TRANS1 a11 JOIN cav_lu_claim_reference a12 ON (a11.claim_ref_id = a12.claim_ref_id)JOIN prt_lu_product a13 ON (a11.Product_id = a13.Product_id)JOIN POt_lu_policy a15 ON (a11.Policy_id = a15.Policy_id)WHERE CT_TYPE_ID <> '90'GROUP BY a12.claim_ref_id, Ca_est_rec_id, transfer_claim_ref, a15.Po_corp_unit_id, currency_id, TRIM(SUBSTR(Claim_line_desc,23,2)), a15.policy_id, a15.policy_desc, SUBSTR(a13.Pr_type_id,2,3);MARC_MCGUCKIAN.FAT_BSE_CL_PAY_CRE_TRANS1 = 2906701 Rowscav_lu_claim_reference = 97622 Rowsprt_lu_product = 474 RowsPOt_lu_policy = 3506973All tables have up to date stats, there are no product joins. I have dropped stats completely and the performance reduced by 10%. There are no other recommended stats from diagnostic helpstats. I don't know what to do next. Any ideas? I rarely use indices. Explanation 1) First, we lock a distinct CQA_data_t."pseudo table" for read on a RowHash to prevent global deadlock for CQA_data_t.PRt_lu_Product. 2) Next, we lock a distinct CQA_data_t."pseudo table" for read on a RowHash to prevent global deadlock for CQA_data_t.POT_LU_POLICY. 3) We lock a distinct CQA_DATA_T."pseudo table" for read on a RowHash to prevent global deadlock for CQA_DATA_T.CAV_LU_CLAIM_REFERENCE. 4) We lock a distinct MARC_MCGUCKIAN."pseudo table" for read on a RowHash to prevent global deadlock for MARC_MCGUCKIAN.a11. 5) We lock a distinct MARC_MCGUCKIAN."pseudo table" for write on a RowHash to prevent global deadlock for MARC_MCGUCKIAN.Rec_Clm_Est_WH_CT1. 6) We lock CQA_data_t.PRt_lu_Product for read, we lock CQA_data_t.POT_LU_POLICY for read, we lock CQA_DATA_T.CAV_LU_CLAIM_REFERENCE for read, we lock MARC_MCGUCKIAN.a11 for read, and we lock MARC_MCGUCKIAN.Rec_Clm_Est_WH_CT1 for write. 7) We execute the following steps in parallel. 1) We do an all-AMPs RETRIEVE step from CQA_data_t.PRt_lu_Product by way of an all-rows scan with no residual conditions into Spool 4 (all_amps), which is duplicated on all AMPs. The size of Spool 4 is estimated with high confidence to be 3,318 rows. The estimated time for this step is 0.01 seconds. 2) We do an all-AMPs RETRIEVE step from MARC_MCGUCKIAN.a11 by way of an all-rows scan with a condition of ( "(MARC_MCGUCKIAN.a11.Ct_type_id <> '90') AND (NOT (MARC_MCGUCKIAN.a11.Claim_ref_id IS NULL ))") into Spool 5 (all_amps), which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The result spool file will not be cached in memory. The size of Spool 5 is estimated with high confidence to be 2,807,964 rows. The estimated time for this step is 19.68 seconds. 8) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an all-rows scan, which is joined to Spool 5 (Last Use) by way of an all-rows scan. Spool 4 and Spool 5 are joined using a single partition hash join, with a join condition of ("Product_id = Product_id"). The result goes into Spool 6 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 6 by row hash. The size of Spool 6 is estimated with low confidence to be 2,807,964 rows. The estimated time for this step is 17.23 seconds. 9) We execute the following steps in parallel. 1) We do an all-AMPs RETRIEVE step from CQA_DATA_T.CAV_LU_CLAIM_REFERENCE by way of an all-rows scan with no residual conditions into Spool 7 (all_amps), which is duplicated on all AMPs. Then we do a SORT to order Spool 7 by row hash. The size of Spool 7 is estimated with high confidence to be 683,354 rows. The estimated time for this step is 2.17 seconds. 2) We do an all-AMPs JOIN step from CQA_data_t.POT_LU_POLICY by way of a RowHash match scan with no residual conditions, which is joined to Spool 6 (Last Use) by way of a RowHash match scan. CQA_data_t.POT_LU_POLICY and Spool 6 are joined using a merge join, with a join condition of ("Policy_id = CQA_data_t.POT_LU_POLICY.Policy_id"). The input table CQA_data_t.POT_LU_POLICY will not be cached in memory, but it is eligible for synchronized scanning. The result goes 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 result spool file will not be cached in memory. The size of Spool 8 is estimated with low confidence to be 2,807,964 rows. The estimated time for this step is 25.17 seconds. 10) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of a RowHash match scan, which is joined to Spool 8 (Last Use) by way of a RowHash match scan. Spool 7 and Spool 8 are joined using a merge join, with a join condition of ("Claim_ref_id = claim_ref_id"). The result goes into Spool 3 (all_amps), which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 3 is estimated with low confidence to be 2,807,964 rows. The estimated time for this step is 5.00 seconds. 11) We do a single-AMP SUM step to aggregate from Spool 3 (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 aggregate spool file will not be cached in memory. The size of Spool 9 is estimated with low confidence to be 2,807,964 rows. The estimated time for this step is 19.21 seconds. 12) We do an all-AMPs RETRIEVE step from Spool 9 (Last Use) by way of an all-rows scan into Spool 1 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 1 by row hash. The result spool file will not be cached in memory. The size of Spool 1 is estimated with low confidence to be 2,807,964 rows. The estimated time for this step is 18.04 seconds. 13) We do an all-AMPs MERGE into MARC_MCGUCKIAN.Rec_Clm_Est_WH_CT1 from Spool 1 (Last Use). 14) We spoil the parser's dictionary cache for the table. 15) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> No rows are returned to the user as the result of statement 1. Mon, 10 Mar 2008 08:30:45 GMTmarcmc