同事的sql跑了几个小时，不用看执行计划就能优化

今天同事说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