今天同事说sql太慢
MERGE INTO FC_BQ_COSTRESULT_PRODUCT A
USING (SELECT SUBSTR(:B1, 1, 4) YEAR,
SUBSTR(:B1, 5, 2) PERIOD,
T1.PK_DEPTDOC,
T1.ROOTDEPT,
T2.ITEMCODE,
T3.PRODUCTCODE,
SUM(NVL(T1.CALMNY, 0)) INDIRITEMVALUE,
(SELECT T.CURRENCY_CODE
FROM DWF_CURRENCY_MAPPING T
WHERE T.PK_CURRTYPE = T1.PK_CURRTYPE) CURRENCY,
(SELECT T.BLCODE
FROM FC_BUSINESSLINE T
WHERE T.PK_BUSINESSLINE = T1.PK_BUSINESSLINE) BUSINESSLINE
FROM FC_COSTGATHERRESULT_M T1
JOIN FC_COSTITEMDEFINE T2 ON T1.PK_COSTITEM = T2.PK_COSTITEM
JOIN FC_PRODUCTDOC T3 ON T1.PK_PRODUCTBALANCE = T3.PK_PRODUCTDOC
WHERE T1.KJND = SUBSTR(:B1, 1, 4)
AND T1.PK_DEPTDOC != T1.ROOTDEPT
AND T1.KJQJ = SUBSTR(:B1, 5, 2)
AND T1.PROD_TYPE = 'N'
AND T1.PK_CALPROJECT = '00011R10000000SLNQZC'
AND T1.PK_COSTITEM != '0001A6100000004YXF9W'
GROUP BY T1.PK_DEPTDOC,
T1.ROOTDEPT,
T2.ITEMCODE,
T3.PRODUCTCODE,
T1.PK_CURRTYPE,
T1.PK_BUSINESSLINE) B
ON (B.ROOTDEPT = A.ROOTDEPT AND A.ITEMCODE = B.ITEMCODE AND A.DEPT = B.PK_DEPTDOC AND A.PRODUCTCODE = B.PRODUCTCODE AND A.CURRENCY = B.CURRENCY AND A.YEAR = B.YEAR AND A.PERIOD = B.PERIOD)
WHEN MATCHED THEN
UPDATE
SET A.INDIRITEMVALUE = B.INDIRITEMVALUE,
A.ITEMVALUE = A.ITEMVALUE + B.INDIRITEMVALUE
WHEN NOT MATCHED THEN
INSERT
(YEAR,
PERIOD,
DEPT,
ROOTDEPT,
ITEMCODE,
PRODUCTCODE,
ITEMVALUE,
INDIRITEMVALUE,
CURRENCY,
DEF2)
VALUES
(B.YEAR,
B.PERIOD,
B.PK_DEPTDOC,
B.ROOTDEPT,
B.ITEMCODE,
B.PRODUCTCODE,
B.INDIRITEMVALUE,
B.INDIRITEMVALUE,
B.CURRENCY,
B.BUSINESSLINE)
一看
(SELECT T.CURRENCY_CODE
FROM DWF_CURRENCY_MAPPING T
WHERE T.PK_CURRTYPE = T1.PK_CURRTYPE) CURRENCY,
(SELECT T.BLCODE
FROM FC_BUSINESSLINE T
WHERE T.PK_BUSINESSLINE = T1.PK_BUSINESSLINE) BUSINESSLINE
问题肯定出在此处
去掉标量,改成jion