Thanks for the guidance. I am using the below query to join 5 tables, and don't want to make a temporary table in between. Is it necessary to break it in between, or making some changes in the local of log can be useful. Query is as follows :- SELECT A.SERIAL_NUM,A.PAR_ASSET_ID,A.POLICY_SOURCE_ID,A.REASON_CD,A.ROOT_ASSET_ID,A.ROW_ID AS ASSET_ID,A.X_MVPN_FLG,A.STATUS_CD,A.END_DT,A.INTEGRATION_ID,A.NAME,A.START_DT, /* --A.SRV_REGN_ID,A.X_GROUP_MEM_FLG,A.X_MNP_PORTOUT_FLAG,A.X_STC_IMSI,A.X_STC_PLATE_ID,A.X_STC_PORT_IN_FLG,A.X_STC_PROM_ID,A.X_STC_SIM_NUM,A.X_STC_SIM_SRV_SUB_TYPE2, */ A.X_STC_SIM_TYPE,A.CREATED,A.ASSET_NUM,A.PROD_ID, /* --A.OWNER_ACCNT_ID,A.SERV_ACCT_ID,A.BILL_ACCNT_ID, */ B.STC_CONTACT_NUMBER, A1.CHAR_VAL AS CATEGORY,A2.CHAR_VAL AS NETWORK_SUPPORT,A3.CHAR_VAL AS MVPN_FLAG,A4.CHAR_VAL AS GROUP_ID,A5.CHAR_VAL AS SUB_GROUP_ID,A6.CHAR_VAL AS CUSTOMER_ID, A7.CHAR_VAL AS MULTI_SIM_FLAG,A8.CHAR_VAL AS RATE_PLAN_VALUE, /* --C.ABA_NUMBER,C.ACCNT_TYPE_CD,C.OU_NUM,C.OU_TYPE_CD,C.PR_CON_ID,C.TICKER_SYM,C.X_ACCOUNT_SUB_TYPE,C.X_MIN_RATE_PLAN_ALLOWED, */ C.OU_NUM AS CUSTOMER_NUMBER ,E.OU_NUM AS BILLING_ACCOUNT, F.OU_NUM AS SERVICE_NO, C.X_STC_BLACKLIST,C.X_COMMERCIAL_REG_NUM, D.PRODUCT_ID , D.NAME AS PRODUCT_NAME,D.ALIAS_NAME,D.PROD_STATUS,D.PROD_CATG_CD FROM EBUSTG.CRMG_M_S_ASSET_ACTIVE A LEFT OUTER JOIN EBUSTG.CRMG_M_X_ASSET_X B ON A.ROW_ID =B.ROW_ID LEFT OUTER JOIN EBUSTG.CRMG_M_S_ASSET_XA A1 ON A.ROW_ID = A1.ASSET_ID AND A1.ATTR_NAME = 'Category' LEFT OUTER JOIN EBUSTG.CRMG_M_S_ASSET_XA A2 ON A.ROW_ID = A2.ASSET_ID AND A2.ATTR_NAME = 'Network Support' LEFT OUTER JOIN EBUSTG.CRMG_M_S_ASSET_XA A3 ON A.ROW_ID = A3.ASSET_ID AND A3.ATTR_NAME = 'MVPN Flag' LEFT OUTER JOIN EBUSTG.CRMG_M_S_ASSET_XA A4 ON A.ROW_ID = A4.ASSET_ID AND A4.ATTR_NAME = 'Group Id' LEFT OUTER JOIN EBUSTG.CRMG_M_S_ASSET_XA A5 ON A.ROW_ID = A5.ASSET_ID AND A5.ATTR_NAME = 'Sub Group Id' LEFT OUTER JOIN EBUSTG.CRMG_M_S_ASSET_XA A6 ON A.ROW_ID = A6.ASSET_ID AND A6.ATTR_NAME = 'Customer Id' LEFT OUTER JOIN EBUSTG.CRMG_M_S_ASSET_XA A7 ON A.ROW_ID = A7.ASSET_ID AND A7.ATTR_NAME = 'Multi SIM Flag' LEFT OUTER JOIN EBUSTG.CRMG_M_S_ASSET_XA A8 ON A.ROW_ID = A8.ASSET_ID AND A8.ATTR_NAME = 'Rate Plan Value' LEFT OUTER JOIN EBUSTG.CRMG_M_S_ORG_EXT C ON A.OWNER_ACCNT_ID = C.PAR_ROW_ID -- AND C.ACCNT_TYPE_CD ='Customer' LEFT OUTER JOIN EBUSTG.CRMG_M_S_ORG_EXT E ON A.BILL_ACCNT_ID = E.PAR_ROW_ID --AND E.ACCNT_TYPE_CD IN ( 'Billing','Prepaid Billing') LEFT OUTER JOIN EBUSTG.CRMG_M_S_ORG_EXT F ON A.SERV_ACCT_ID = F.PAR_ROW_ID --AND F.ACCNT_TYPE_CD ='Service' LEFT OUTER JOIN (SELECT ROW_ID AS PRODUCT_ID,NAME,ALIAS_NAME,EFF_END_DT,PROD_CATG_CD,'Active' as PROD_STATUS FROM EBUSTG.CRMG_M_S_PROD_INT WHERE EFF_END_DT IS NULL) D ON A.PROD_ID =D.PRODUCT_ID WHERE (TRIM(LEADING '0' FROM A.SERIAL_NUM ) LIKE '5%' OR TRIM(LEADING '0' FROM A.SERIAL_NUM ) LIKE '8%') AND A.ROW_ID = A.ROOT_ASSET_ID;
... View more