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;
