세미나 질문과 답변
SELECT CASE WHEN GROUPING(T.ITEM_BRANCH_LCODE) = 1 THEN PT_TOTAL_SUM
WHEN GROUPING(T.ITEM_TYPE_LCODE) = 1 THEN T.ITEM_BRANCH_LCODE
ELSE T.ITEM_BRANCH_LCODE
END AS ITEM_BRANCH_LCODE
, CASE WHEN GROUPING(T.ITEM_BRANCH_LCODE) = 1 THEN ' '
WHEN GROUPING(T.ITEM_TYPE_LCODE) = 1 THEN PT_SUB_SUM
ELSE EAPP_COMMON_G.GET_LOOKUP_DESC(W_SOB_ID, W_ORG_ID, 'ITEM_TYPE', T.ITEM_TYPE_LCODE)
END AS ITEM_TYPE_DESC
, T.BOM_ITEM_CODE
, T.BOM_ITEM_DESCRIPTION
, T.ITEM_LAYER_LCODE
, T.JOB_NO
, T.ITEM_UOM_CODE
, SUM(T.TOTAL_RELEASE_QTY) AS TOTAL_RELEASE_QTY
, SUM(T.TOTAL_REJECT_QTY) AS TOTAL_REJECT_QTY
, CASE WHEN SUM(T.TOTAL_RELEASE_QTY) > 0
THEN ROUND( SUM(T.TOTAL_REJECT_QTY)
/ SUM(T.TOTAL_RELEASE_QTY)
,4) * 100
ELSE 0
END AS TOTAL_REJECT_RATE
, SUM(T.GENERAL_RELEASE_QTY) AS GENERAL_RELEASE_QTY
, SUM(T.GENERAL_REJECT_QTY) AS GENERAL_REJECT_QTY
, CASE WHEN SUM(T.GENERAL_RELEASE_QTY) > 0
THEN ROUND( SUM(T.GENERAL_REJECT_QTY)
/ SUM(T.GENERAL_RELEASE_QTY)
,4) * 100
ELSE 0
END AS GENERAL_REJECT_RATE
, SUM(T.FINAL_INSP_RELEASE_QTY) AS FINAL_INSP_RELEASE_QTY
, SUM(T.FINAL_INSP_REJECT_QTY) AS FINAL_INSP_REJECT_QTY
, CASE WHEN SUM(T.FINAL_INSP_RELEASE_QTY) > 0
THEN ROUND( SUM(T.FINAL_INSP_REJECT_QTY)
/ SUM(T.FINAL_INSP_RELEASE_QTY)
,4) * 100
ELSE 0
END AS FINAL_INNSP_REJECT_RATE
, SUM(T.SMD_RELEASE_QTY) AS SMD_RELEASE_QTY
, SUM(T.SMD_REJECT_QTY) AS SMD_REJECT_QTY
, CASE WHEN SUM(T.SMD_RELEASE_QTY) > 0
THEN ROUND( SUM(T.SMD_REJECT_QTY)
/ SUM(T.SMD_RELEASE_QTY)
,4) * 100
ELSE 0
END AS SMD_REJECT_RATE
, SUM(T.SPBA_RELEASE_QTY) AS SPBA_RELEASE_QTY
, SUM(T.SPBA_REJECT_QTY) AS SPBA_REJECT_QTY
, CASE WHEN SUM(T.SPBA_RELEASE_QTY) > 0
THEN ROUND( SUM(T.SPBA_REJECT_QTY)
/ SUM(T.SPBA_RELEASE_QTY)
,4) * 100
ELSE 0
END AS SPBA_REJECT_RATE
, SUM(T.PACKING_RELEASE_QTY) AS PACKING_RELEASE_QTY
, SUM(T.PACKING_REJECT_QTY) AS PACKING_REJECT_QTY
, CASE WHEN SUM(T.PACKING_RELEASE_QTY) > 0
THEN ROUND( SUM(T.PACKING_REJECT_QTY)
/ SUM(T.PACKING_RELEASE_QTY)
,4) * 100
ELSE 0
END AS PACKING_REJECT_RATE
, T.BOM_ITEM_ID
, T.JOB_ID
FROM (SELECT 중간 서브쿼리 셀렉트절 생략
FROM WIP_JOB_ENTITIES WJE
, SDM_ITEM_REVISION SIR
, INV_ITEM_MASTER IIM
, OE_SALES_ORDER_LINE SOL
, OE_SALES_ORDER_HEADER SOH
, FI_VENDOR VEN
WHERE SIR.BOM_ITEM_ID = WJE.BOM_ITEM_ID
AND IIM.INVENTORY_ITEM_ID = SIR.INVENTORY_ITEM_ID
AND SOL.ORDER_LINE_ID = WJE.ORDER_LINE_ID
AND SOH.ORDER_HEADER_ID = SOL.ORDER_HEADER_ID
AND VEN.VENDOR_ID = SOH.BILL_TO_CUST_SITE_ID
AND WJE.SOB_ID = W_SOB_ID
AND WJE.ORG_ID = W_ORG_ID
AND WJE.BOM_ITEM_ID = W_BOM_ITEM_ID
AND EXISTS( SELECT 'Y'
FROM WIP_MOVE_TRANSACTIONS MT
, SDM_STANDARD_OPERATION SO
WHERE MT.FROM_OPERATION_ID = SO.OPERATION_ID
AND MT.JOB_ID = WJE.JOB_ID
AND MT.MOVE_TRX_TYPE = 'RUN_END'
AND SO.OPERATION_CODE = '4520'
AND MT.MOVE_TRX_DATE BETWEEN V_TRX_DATE_FR
AND V_TRX_DATE_TO
)
) TRX
) T
GROUP BY ROLLUP
( ( T.ITEM_BRANCH_LCODE )
, ( T.ITEM_TYPE_LCODE
, T.BOM_ITEM_CODE
, T.BOM_ITEM_DESCRIPTION
, T.ITEM_LAYER_LCODE
, T.JOB_NO
, T.VENDOR_CODE
, T.VENDOR_SHORT_NAME
, T.ITEM_UOM_CODE
, T.BOM_ITEM_ID
, T.JOB_ID)
)
ORDER BY T.ITEM_BRANCH_LCODE
, T.ITEM_TYPE_LCODE
, T.BOM_ITEM_CODE
;
코딩이 장황하지만 그냥 별건 없고... 서브쿼리 두번 타고요...
문제는 마지막 하위쿼리에 EXISTS 부분을 생략하고 돌리면 조회가 상당히 빠릅니다.
근데 EXISTS 절을 넣으면 조회가 5분이상 걸리고요...
반대로 ROLLUP 을 생략하면 빨라지네요
이그지스트와 롤업의 모종의 계략이 있는것같기도하고 SUM때문에 느려진다는 분도있는데.
이유야 어찌됐건 전 Rollup과 exists 를 만족해야만 하거든요.. 상당한 고민이랍니다..
누가 이 시원한 문제를 조언해주실분 없을까요?
-- With 구문을 이용해 보세요 --
WITH t AS
(
SELECT /*+ materialize */
-- 중간 서브쿼리 셀렉트절 생략 --
)
SELECT ...
FROM t
GROUP BY ROLLUP (...)
;
) 버튼을 클릭하여 작성 하시면 됩니다.