CodeSOD: Sea of SQL

Andy writes: "Operations reported that a query was taking a long time. Even the 'developers' of this query didn't know why it was taking a long time."

I tell ya, folks" some submissions, you just set down and back away slowly" then hunt up a magnifying glass and a bottle of aspirin.

SELECT /*+ optimizer_features_enable('9.2.0') */ N1.ORDER_ID AS ORDER_ID, N1.N1_STATUS_ID AS N1_STATUS_ID, N1.SKU_ID AS SKU_ID, O.SITE_ID AS SITE_ID, O.ORDER_DT AS ORDER_DT, O.D_TOTAL_PRICE AS PRICE, RS.LEGAL_NAME AS RESELLER_LEGAL_NAME, PRS.LEGAL_NAME AS PARENT_RESELLER_LEGAL_NAME, S.COMPANY_NAME AS SITE_COMPANY_NAME, O.ORDER_STATE_ID AS ORDER_STATE_ID, ITEM.COUNTRY AS ITEM_COUNTRY, E.COUNTRY AS E_COUNTRY, C.CUST_SERVICE_REGION AS REGION, RS.PARTNER_LEVEL, O.FOLLOWUP_DT, N1.APPROVER_CONFIRM_DT, 'N' AS REISSUE_IND, sign(nvl(sum(distinct(R.ORDER_ID * ( -(SIGN(NVL(R.OVERRIDE_PERSON_ID, 0)) -1)) )), 0)) AS RSRC_CTRL_VIOLATION, max(decode( p.email_address, :1, 'Y', 'N' )) AS MANUAL_APPROVAL_IND, max(nvl(R.RESOURCE_CTRL_TYPE_ID, 0)) AS RESOURCE_CTRL_TYPE_ID , nvl(CFA.POLL_STATUS, 'N/A') AS POLL_STATUS , CFA.APPROVE_METHOD FROM ORDERS_N1 N1, ORDERS O, ENTERPRISE E, ENTERPRISE RS, ENTERPRISE PRS, SITE S, ORDERS_RSRC_CTRL R, PERSON P, COUNTRY C, SITE_ITEM ITEM , C ERT_FILE_APPROVE_DV_T CFA WHERE N1.ORDER_ID = O.ORDER_ID AND N1.N1_STATUS_ID 3 AND N1.N1_STATUS_ID 10 AND O.CUST_ENTERPRISE_ID = E.ENTERPRISE_ID AND O.RESELLER_ENTERPRISE_ID = RS.ENTERPRISE_ID AND RS.RESELLER_REFERRED_BY_ENT_ID = PRS.ENTERPRISE_ID AND O.SITE_ID = S.SITE_ID AND O.ORDER_ID = R.ORDER_ID(+) AND O.ORDER_COMPLETE_IND = 'N' AND O.ORDER_ID = ITEM.ORDER_ID AND ITEM.ITEM_ID = CFA.ITEM_ID (+) AND (ITEM.COUNTRY IS NOT NULL AND C.COUNTRY_CD = ITEM.COUNTRY OR ITEM.COUNTRY IS NULL AND C.COUNTRY_CD = E.COUNTRY) AND O.ORDER_DT >= to_date(:2, 'mmddyyyyhh24miss') AND O.ORDER_DT 220 GROUP BY N1.ORDER_ID, N1.N1_STATUS_ID, N1.SKU_ID, O.SITE_ID, O.ORDER_DT, O.D_TOTAL_PRICE, RS.LEGAL_NAME, PRS.LEGAL_NAME, S.COMPANY_NAME, O.ORDER_STATE_ID, ITEM.COUNTRY, E.COUNTRY, C.CUST_SERVICE_REGION, RS.PARTNER_LEVEL, O.FOLLOWUP_DT, N1.APPROVER_CONFIRM_DT, CFA.POLL_STATUS , CFA.APPROVE_METHOD UNION SELECT /*+ optimizer_features_enable('9.2.0') */ N1.ORDER_ID AS ORDER_ID, N1.N1_STATUS_ID AS N1_STATUS_ID, N1.SKU_ID AS SKU_ID, O.SITE_ID AS SITE_ID, O.ORDER_DT AS ORDER_DT, O.D_TOTAL_PRICE AS PRICE, RS.LEGAL_NAME AS RESELLER_LEGAL_NAME, PRS.LEGAL_NAME AS PARENT_RESELLER_LEGAL_NAME, S.COMPANY_NAME AS SITE_COMPANY_NAME, O.REISSUE_ORDER_STATE_ID AS ORDER_STATE_ID, ITEM.COUNTRY AS ITEM_COUNTRY, E.COUNTRY AS E_COUNTR Y, C.CUST_SERVICE_REGION AS REGION, RS.PARTNER_LEVEL, O.FOLLOWUP_DT, N1.APPROVER_CONFIRM_DT, 'Y' AS REISSUE_IND, sign(nvl(sum(distinct(R.ORDER_ID * ( -(SIGN(NVL(R.OVERRIDE_PERSON_ID, 0)) -1)) )), 0)) AS RSRC_CTRL_VIOLATION, max(decode( p.email_address, :5, 'Y', 'N' )) AS MANUAL_APPROVAL_IND, max(nvl(R.RESOURCE_CTRL_TYPE_ID, 0)) AS RESOURCE_CTRL_TYPE_ID , nvl(CFA.POLL_STATUS, 'N/A') AS POLL_STATUS , CFA.APPROVE_METHOD FROM ORDERS_N1 N1, ORDERS O, ENTERPRISE E, ENTERPRISE RS, ENTERPRISE PRS, SITE S, ORDERS_RSRC_CTRL R, PERSON P, COUNTRY C, SITE_ITEM ITEM , ITEM_FILE_APPROVE_DV_T CFA WHERE N1.ORDER_ID = O.ORDER_ID AND O.CUST_ENTERPRISE_ID = E.ENTERPRISE_ID AND O.RESELLER_ENTERPRISE_ID = RS.ENTERPRISE_ID AND RS.RESELLER_REFERRED_BY_ENT_ID = PRS.ENTERPRISE_ID AND O.SITE_ID = S.SITE_ID AND O.ORDER_ID = R.ORDER_ID(+) AND O.ORDER_ID = ITEM.ORDER_ID AND ITEM.ITEM_ID = CFA.ITEM_ID (+) AND ITEM.ITEM_STATUS_ID = 5 AND (ITEM.COUNTRY IS NOT NULL AND C.COUNTRY_CD = ITEM.COUNTRY OR C ERT.COUNTRY IS NULL AND C.COUNTRY_CD = E.COUNTRY) AND ITEM.START_DT >= to_date(:6, 'mmddyyyyhh24miss') AND ITEM.START_DT 220 GROUP BY N1.ORDER_ID, N1.N1_STATUS_ID, N1.SKU_ID, O.SITE_ID, O.ORDER_DT, O.D_TOTAL_PRICE, RS.LEGAL_NAME, PRS.LEGAL_NAME, S.COMPANY_NAME, O.REISSUE_ORDER_STATE_ID, ITEM.COUNTRY, E.COUNTRY, C.CUST_SERVICE_REGI ON, RS.PARTNER_LEVEL, O.FOLLOWUP_DT, N1.APPROVER_CONFIRM_DT, CFA.POLL_STATUS , CFA.APPROVE_METHOD ORDER BY N1_STATUS_ID, ORDER_DT DESC

[Advertisement] Use NuGet or npm? Check out ProGet, the easy-to-use package repository that lets you host and manage your own personal or enterprise-wide NuGet feeds and npm repositories. It's got an impressively-featured free edition, too!