ZHT_ORD_RCV最大大概22万条记录,其他表都不大,这个语句执行时间大概6-7分钟,有没有办法优化?给点线索就行了,谢谢SQL语句 SELECT
RCV.RCV_NO
, RCV.RCV_LINENO
, RCV.ITEM_CD
, RCV.ITEM_NM
, RCV.ITEM_EX_INFO
,CSM030004.CONVERT_QTY(RCV.ITEM_CD,MINI.ITEM_UOM_CD,null,RCV.RCV_ITEM_UOM_CD,null,ACT.ITEM_UNIT_QTY * ACT.QTY - NVL(CHK.QTY,0),1) AS CENTER_AC
, NVL(TRUNC(INV.ITEM_UNIT_QTY
/ RCV.RCV_ITEM_UNIT_QTY
* INV.INV_QTY), 0) OC_ACT
, NVL(RCV.RCV_QTY, 0) RCV_QTY
, NVL(RCV.DIR_QTY, 0) DIR_QTY
, RCV.DIR_ITEM_UOM_NM
, NVL(RCV.DIR_DISP_ITEM_UNIT_QTY, 0) DIR_DISP_ITEM_UNIT_QTY
, NVL(RCV.WHOLESALE_RATE, 0) WHOLESALE_RATE
, NVL(RCV.STORE_PRICE_DIR, 0) STORE_PRICE_DIR
, CSM030004.ROUND_AMT(NVL(RCV.STORE_AMT_DIR, 0)) STORE_AMT_DIR
, NVL(RCV.SHP_QTY, 0) SHP_QTY
, NVL(RCV.INSPECT_QTY, 0) INSPECT_QTY
, RCV.DEL_FLG
, NVL(LMT.QTY_LIMIT_AMT, -1) QTY_LIMIT_AMT
, NVL(LMT.ORD_CONTROL_AMT, 0) ORD_CONTROL_AMT
, RCV.RCV_UP_DTTM
, RCV.UP_DTTM
, IEX.ALLOCATE_CHK_FLG
, ATTV.ATTACHMENT_ITEM_CD
FROM
(
SELECT
ORD.STR_CD
, ORD.DLV_CD
, ORD.UP_DTTM RCV_UP_DTTM
, RCV.*
FROM
ZHT_ORD_RCV ORD
, ZHT_ORD_RCV_DETAIL RCV
WHERE
ORD.RCV_NO = '62981'
AND RCV.RCV_NO = ORD.RCV_NO
) RCV
, ZGM_ITEM_EX IEX
, HT_ACTUAL_QTY ACT
, (
SELECT
ITEM_CD
,SUM(QTY) AS QTY
,STR_CD
FROM
(
SELECT
RCD.ITEM_CD
,SUM(NVL(RCD.DIR_QTY, 0) * NVL(RCD.DIR_ITEM_UNIT_QTY, 0)) QTY
,RCV.STR_CD
FROM
ZHT_ORD_RCV RCV
, ZHT_ORD_RCV_DETAIL RCD
WHERE
RCD.RCV_NO = RCV.RCV_NO
AND RCD.SET_FLG = 0
AND RCD.DEL_FLG = 0
AND RCV.RCV_STS IN ('1','2')
AND RCV.DEL_FLG = 0
AND RCV.TRAN_DIV NOT IN ('22', '02')
GROUP BY
RCD.ITEM_CD
,RCV.STR_CD
UNION ALL
SELECT
STI.SUB_ITEM_CD ITEM_CD
, SUM(NVL(RCD.DIR_QTY, 0) * NVL(STI.ITEM_UNIT_QTY, 0) * NVL(STI.RCP_QTY, 0)) QTY
, RCV.STR_CD
FROM
ZHT_ORD_RCV RCV
, ZHT_ORD_RCV_DETAIL RCD
, ZHT_ORD_ITEM_DETAIL STI
WHERE
RCD.RCV_NO = RCV.RCV_NO
AND RCD.SET_FLG = 1
AND RCD.DEL_FLG = 0
AND STI.RCV_NO = RCD.RCV_NO
AND STI.RCV_LINENO = RCD.RCV_LINENO
AND STI.SET_ITEM_CD = RCD.ITEM_CD
AND RCV.RCV_STS IN ('1','2')
AND RCV.DEL_FLG = 0
AND RCV.TRAN_DIV NOT IN ('22', '02')
GROUP BY
STI.SUB_ITEM_CD
,RCV.STR_CD
)
GROUP BY
ITEM_CD
,STR_CD
) CHK
, (
SELECT
INV.STR_CD
, INV.ITEM_CD
, INV.ITEM_UNIT_QTY
, INV.INV_QTY
FROM
HT_INVENTORY INV
, (
SELECT
MAX(INV.INV_STD_DT) INV_STD_DT
, INV.STR_CD
, INV.ITEM_CD
FROM
HT_INVENTORY INV
GROUP BY
INV.STR_CD
, INV.ITEM_CD
) INVM
WHERE
INVM.STR_CD = INV.STR_CD
AND INVM.ITEM_CD = INV.ITEM_CD
AND INVM.INV_STD_DT = INV.INV_STD_DT
) INV
, ZGM_QTY_LIMIT LMT
, (SELECT * FROM GM_ITEM_UOM WHERE MIN_UOM_FLG = 1) MINI
, ZGM_ATTACHMENT_V ATTV
WHERE
ACT.STR_CD(+) = RCV.STR_CD
AND ACT.ITEM_CD(+) = RCV.ITEM_CD
AND CHK.STR_CD(+) = RCV.STR_CD
AND CHK.ITEM_CD(+) = RCV.ITEM_CD
AND MINI.ITEM_CD = RCV.ITEM_CD
AND INV.STR_CD(+) = RCV.DLV_CD
AND INV.ITEM_CD(+) = RCV.ITEM_CD
AND LMT.ITEM_CD(+) = RCV.ITEM_CD
AND LMT.ITEM_UOM_CD(+) = RCV.DIR_ITEM_UOM_CD
AND LMT.CHANGE_DT(+) <= SYSDATE
AND LMT.END_DT(+) >= SYSDATE
AND RCV.ITEM_CD = IEX.ITEM_CD
AND RCV.ITEM_CD = ATTV.ATTACHMENT_ITEM_CD(+)
ORDER BY
RCV.RCV_NO
, RCV.RCV_LINENO
;