SELECT DISTINCT
TASK.RDB_POOLING_ID,TASK.FTN_NBR,TASK.M5_ORDR_ID,TASK.CUST_NME,TASK.HI_ID,TASK.H6_ID,TASK.NUA_ADR,TASK.SPA_ETH_CD,TASK.ORDR_TYPE_NME, TASK.PROD_TYPE_NME,TASK.ACTY_TYPE_NME,TASK.SCNRO_NME,
CASE WHEN TASK.TASK_CRET_DT IS NOT NULL THEN TO_CHAR(TASK.TASK_CRET_DT,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END ORDR_RECV_DT,
CASE WHEN ORDR.CUST_CMMT_DT IS NOT NULL THEN TO_CHAR(ORDR.CUST_CMMT_DT,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END CUST_WANT_DT,TASK.DVSTY_NME,TASK.VNDR_NME,TASK.QUEUE_NME,TASK.TASK_NME,TASK.TASK_ID,
CASE WHEN TASK.TASK_CRET_DT IS NOT NULL THEN TO_CHAR(TASK.TASK_CRET_DT+1/24,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END TASK_CRET_DT,
TASK.INITL_ASN_TO_NME,TASK.ASN_TO_NME,
CASE WHEN TASK.REASN_DT IS NOT NULL THEN TO_CHAR(TASK.REASN_DT,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END REAS_DT,
CASE WHEN TASK.EST_CMPLT_DT IS NOT NULL THEN TO_CHAR(TASK.EST_CMPLT_DT,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END EST_CMPLT_DT,
round((select new_time( sysdate, 'EST', 'GMT') from dual)- TASK_CRET_DT,0)SLA_NBR,
TASK.TASK_STUS_NME,TASK.TASK_CMPLT_DT,TASK.PORT_SPED_NME,TASK.SCA_NBR,TASK.REAS_CD,TASK.CMNT_TXT,TASK.REQ_TYPE_NME,TASK.ACCS_IMPLMTN_ GRP_NME,
CASE WHEN TASK.CREAT_DT IS NOT NULL THEN TO_CHAR(TASK.CREAT_DT,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END CREAT_DT,
CASE WHEN TASK.MOD_DT IS NOT NULL THEN TO_CHAR(TASK.MOD_DT,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END MOD_DT,TASK.L2_TAG_NME,TASK.IPV4_ADR,TASK.IPV6_ADR,TASK.CXR_NME,TASK.RSRC_NME,
TASK.FMS_CKT_ID,TASK.DD_NBR,TASK.NRM_SO_NME,TASK.NRM_SO_OBJ_ID,NVL(ORDR.PRE_QUAL_NBR,NRM_ORDR.PREQUAL_LINE_ITEM_ID)PRE_QUAL_NBR,
(select DSGN_CNTL_FOLDR_LOC_NME from DD_INTRO where DD_NBR = TASK.DD_NBR AND ROWNUM =1) DSGN_CNTL_FOLDR_LOC_NME,
TASK.TASK_CRET_DT RECV_DT,ORDR.CUST_CMMT_DT TASK_CUST_WANT_DT,TASK.TASK_CRET_DT TASK_CREAT_DT,TASK.REASN_DT TASK_REAS_DT,TASK.EST_CMPLT_DT TASK_EST_CMPLT_DT,TASK.MOD_DT TASK_MOD_DT,SITE.RGN_NME,SITE.LOC_NME,SITE.SITE_ID,
SITE.TOC_NME,TASK.VRF_NME VRF_NME,NULL VRF_ID,NVL(TASK.VLAN_ID,TASK.DLCI_ID)VLAN_ID,TASK.M5_ORDR_TYPE_CD,TASK.M5_ORDR_SUB_TYPE_CD,TASK.RELTD_M5_ORDR_ID,TASK.RELTD_M5_ORDR_N BR,TASK.ENT_TYPE_NME,TASK.TASK_PRCS_ID,TASK.EXP_CD,
TASK.EXP_DT,TASK.EXP_USER_NME,CUST_ACCT.CSG_LVL CSG_LVL_CD,
NVL((SELECT case when SCA_NBR IS NOT NULL then 'Yes' else 'No'end
FROM BPMF_NRM_ORDR WHERE FTN_NBR = TASK.FTN_NBR AND SRVC_CAT_NME = 'IP Services' and rownum =1)
,(SELECT CASE WHEN APRV_SCA_NME IS NOT NULL THEN 'Yes' else 'No' end
from DD_OPPORTUNITY where dd_nbr = TASK.dd_nbr and rownum =1))SCA_FLG_CD,
NRM_ORDR.SCA_CD,
CASE WHEN NRM_ORDR.PROD_ID like '%Ethernet%' THEN 'Yes' ELSE 'No' END SPA_CD,
CUST_ACCT.CITY CITY_NME,CUST_ACCT.STATE_PROV STATE_PRVN_CD,CUST_ACCT.CTRY_CD,NRM_ORDR.ETHRNT_ACCS_TYPE_NME PRICE_TYPE_NME,TASK.TASK_ASN_DT,
CASE WHEN TASK.TASK_ASN_DT IS NOT NULL THEN TO_CHAR(TASK.TASK_ASN_DT,'mm/dd/yyyy hh24:mi:ss')||' EST' ELSE '' END TASK_ASN_DT_TXT,
NRM_ORDR.ASYMET_CD,NRM_ORDR.ETHRNT_MONTR_CD,NRM_ORDR.SYMET_SPD_NME,NRM_ORDR.ASYMET_SPD_NME,NULL EIGRP_FLAG_CD,NRM_ORDR.EXP_CD M5_EXP_CD,TASK.RFS_ORDR_ID
FROM DD_RDB_POOLING_TASK_DETL TASK
LEFT JOIN BPMF_NRM_ORDR NRM_ORDR ON NRM_ORDR.FTN_NBR = TASK.FTN_NBR
LEFT JOIN M5_ORDR ORDR ON ORDR.ORDER_NBR = TASK.FTN_NBR
LEFT JOIN (SELECT PQ.PREQUAL_LINE_ITEM_ID,INTL.RGN_NME,INTL.LOC_NME,INTL.SITE_ID,INTL.TOC_NME
FROM PREQUAL_PRS_DATA PQ,INTL_SITE_LOC INTL
WHERE INTL.site_id = trim(substr(PQ.accs_city_site_nme,1,5))
)SITE on SITE.PREQUAL_LINE_ITEM_ID = ORDR.PRE_QUAL_NBR
LEFT JOIN M5_CUST_ACCT CUST_ACCT ON CUST_ACCT.CUST_ACCT_ID = ORDR.PRNT_ACCT_ID
WHERE (TASK.FTN_NBR IS NOT NULL OR TASK.NUA_ADR IS NOT NULL);
The query goes for FULL Table scan on those LEFT Join, I attached the Explain plan. Any suggestions are most welcome.
|
] 
Blog
Search
Help
Members
Register
Login
Home
