这个sql执行跑不出来，耗时太多，求优化建议？

一杯鲜橙多 2015-12-09 04:29:41

requirement:
1) PO with SO#, no Post# AND
2) Units Ordered not equal to Units Booked AND
3) SO create/update date = report generation date - 1
OR
1) PO with SO# AND
2) PO with Post# AND
3) Units Ordered not equal to Units Booked OR Units Ordered not equal to Units Shipped AND
4) SO or Post create/update date = report generation date - 1

Sql:

SELECT  PO_CUST_NAME "Consignee",

        PO_NUM "PO#",

        PO_ITEM_NUM "SKU#",

        PO_ITEM_TOTAL_QTY "Units Ordered",

        SO_ITEM_TOTAL_UNIT "Units Booked",

        PSTADV_ITEM_TOTAL_UNIT "Units Shipped",

        SO_NUM "SO#",

        PSTADV_NUM "Post Advice#",

        PSTADV_CARRIER_BL_NUM "FW-BL"

 FROM(  (SELECT SO.SO_CUST_NAME AS PO_CUST_NAME,

        SO.SO_ITEM_PO_NUM  AS PO_NUM,

        SO.SO_ITEM_NUM AS PO_ITEM_NUM,

        PO.PO_ITEM_TOTAL_QTY AS PO_ITEM_TOTAL_QTY,

        SO.SO_ITEM_TOTAL_UNIT AS SO_ITEM_TOTAL_UNIT,

        NULL  AS PSTADV_ITEM_TOTAL_UNIT,

        SO.SO_NUM AS SO_NUM,

        NULL AS PSTADV_NUM,

        NULL AS PSTADV_CARRIER_BL_NUM

      FROM RPT_SHIP_DETAIL_PO PO,

          RPT_SHIP_DETAIL_SO SO

      WHERE PO.SHIP_DETAIL_PO_OID = SO.SHIP_DETAIL_PO_OID

      AND NOT EXISTS

        (SELECT 1

        FROM RPT_SHIP_DETAIL_PSTADV PSTADV

        WHERE PSTADV.SHIP_DETAIL_SO_OID = SO.SHIP_DETAIL_SO_OID)

      AND SO.SO_PARENT_CUST_CODE = '10000512CRR'

      AND SO.SO_CUST_CODE IN ('10000516CRR', '10000514CRR', '10000515CRR', '10000512CRR')

      AND PO.PO_ITEM_TOTAL_QTY <> SO.SO_ITEM_TOTAL_UNIT

      AND SO.SO_CREATE_DATE = (trunc(sysdate) - 1)

      AND (SO.SO_STATUS          IS NULL

        OR SO.SO_STATUS            <> 'CANCELLED')

      )

      UNION ALL

      (SELECT PSTADV.PSTADV_CUST_NAME  AS PO_CUST_NAME,

        PSTADV.PSTADV_ITEM_PO_NUM AS PO_NUM,

        SO.SO_ITEM_NUM AS PO_ITEM_NUM,

        PO.PO_ITEM_TOTAL_QTY AS PO_ITEM_TOTAL_QTY,

        SO.SO_ITEM_TOTAL_UNIT AS SO_ITEM_TOTAL_UNIT,

        PSTADV.PSTADV_ITEM_TOTAL_UNIT AS PSTADV_ITEM_TOTAL_UNIT,

        SO.SO_NUM AS SO_NUM,

        PSTADV.PSTADV_NUM AS PSTADV_NUM,

        PSTADV.PSTADV_CARRIER_BL_NUM AS PSTADV_CARRIER_BL_NUM

        FROM RPT_SHIP_DETAIL_PO PO,

          RPT_SHIP_DETAIL_SO SO,

          RPT_SHIP_DETAIL_PSTADV PSTADV

        WHERE SO.SHIP_DETAIL_SO_OID = PSTADV.SHIP_DETAIL_SO_OID

        AND PO.SHIP_DETAIL_PO_OID   = SO.SHIP_DETAIL_PO_OID

        AND PSTADV.PSTADV_PARENT_CUST_CODE = '10000512CRR'

        AND PSTADV.PSTADV_CUST_CODE  IN ('10000516CRR', '10000514CRR', '10000515CRR', '10000512CRR')

        AND (PO.PO_ITEM_TOTAL_QTY <> SO.SO_ITEM_TOTAL_UNIT OR PO.PO_ITEM_TOTAL_QTY <>PSTADV.PSTADV_ITEM_TOTAL_UNIT)

        AND SO.SO_CREATE_DATE = (trunc(sysdate) - 1)

        AND (SO.SO_STATUS          IS NULL

        OR SO.SO_STATUS            <> 'CANCELLED')

        ) )

      ORDER BY PO_CUST_NAME,

               PO_NUM,

               PO_ITEM_NUM