TGT.Account_Num ,TGT.Account_Modifier_Num ,TGT.Customer_Asset_Id ,TGT.Asset_Contract_Role_Cd ,TGT.Agreement_Asset_Start_Dt ,TGT.Agrmt_Collateral_Asset_Amt ,TGT.Acct_Curr_Agrmt_Collat_Ass_Amt ,TGT.Agrmt_Collateral_Asset_Pct ,TGT.Agrmt_Asset_Repossession_Dt ,TGT.Basis_of_Adv_Asset_Val_Type_Cd FROM DS0_VEDW.V0315_ACCT_COLLATERAL_ASSET TGT INNER JOIN DS0_FCMDM.DATA_SOURCES_FILTER B ON COALESCE(TRIM(TGT.DATA_SOURCE_CD),'') = COALESCE(TRIM(B.DATA_SOURCE_CD),'' ) AND TGT.End_Dt is null;
In the above query join condition,the DATA_SOURCE_CD in TGT table is not the primary or secondary index and the column DATA_SOURCE_CD in B table is a UPI. when we retrieve the data from both tables based on the join condition COALESCE(TRIM(TGT.DATA_SOURCE_CD),'') = COALESCE(TRIM(B.DATA_SOURCE_CD),'' ),i have the following questions:
1) How the data redistribution will happen if left hand side is not an index column and right hand side is index column? 2) If the above join condition is not correct then what is the other alternative ? (Join index is not preferrable here). 3) what is the use of COALESCE in the join condition? (Is it improve the performance here?) 4) any other possibility to re-write the above query to improve performance?
Hi, you can check on the on which keys are used for the redistribution in the explain plan (at least on TD12). Very likly the tables will be redistributed along data_source_id...is it a problem ? How about the distibution of data_source_id ? We also had bad problems on joins using coalesce...is it necessary? the UPI should not be null at all....