记一次标量子查询的优化

评论
466 views

今天一个同事扔来一条SQL，反映说11s才出结果，前台有个10s的超时设置，问能不能优化下，降到10s以内。SQL Code如下：

SELECT / system-mng-mapper | selectPtnDispatchbillInsert  /
            STKI_CMD_ID         --采购单号
            ,WH_NO              --仓库号
            ,WH_NM              --仓库名
            ,STKI_CMD_PGS_STS_NM    --执行状态
            ,PRD_ID             --商品号
            ,PRD_NM             --商品名
            ,MD_ID              --MD号
            ,MD_NM              --MD名
            ,STKI_CMD_PTR_NM    --采购类型
            ,PRS_SP_NM          --合作方式
            ,PRD_PTR_NM         --商品类型
            ,STKI_CMD_DATE      --采购指示日
            ,STKI_DDL_DATE      --采购到期日
            ,ORG_STKI_CMD_ID    --原采购单号
            ,STKI_CMD_REQ_ID    --采购申请号
            ,STOCK_MDA_NM       --库存渠道
            ,WH_PTR_NM          --入库属性
            ,STKI_CMD_QTY                                                          AS STKI_CMD_QTY         --采购数量
            ,(A.STKI_CMD_SO_ARR_QTY - A.STKI_CMD_CNFM_QTY - A.STKI_CMD_QA_FLR_QTY) AS STKI_SO_PGS_QTY      --发货在途数量
            ,STKI_CMD_CNFM_QTY                                                     AS STKI_CNFM_QTY        --入库数量            
            ,(A.STKI_CMD_QTY - (A.STKI_CMD_SO_ARR_QTY - A.STKI_CMD_QA_FLR_QTY))    AS STKI_RMN_QTY         --剩余数量   
        FROM
        (SELECT
             PG_FUL_UTIL.FN_FUL_CMD_MASK(FSCM.STKI_CMD_ID) AS STKI_CMD_ID
            ,FSCM.WH_NO
            ,(SELECT WH_NM FROM FUL_WH_M WHERE FUL_WH_M.WH_NO = FSCM.WH_NO) WH_NM
            ,(SELECT CODE_NM FROM CMM_CODE_D WHERE CODE_GRP_ID = 'FUL005' AND CMM_CODE_D.CODE = FSCM.STKI_CMD_PGS_STS_CD) STKI_CMD_PGS_STS_NM
            ,FSCM.PRD_ID
            ,(SELECT PRD_NM FROM PRD_PRD_M WHERE PRD_PRD_M.PRD_ID = FSCM.PRD_ID) AS PRD_NM
            ,FSCM.MD_ID
            ,(SELECT MD_NM FROM PRD_MD_M WHERE PRD_MD_M.MD_ID = FSCM.MD_ID) AS MD_NM
            ,(SELECT CODE_NM FROM CMM_CODE_D WHERE CODE_GRP_ID = 'FUL006' AND CMM_CODE_D.CODE = FSCM.STKI_CMD_PTR_CD) STKI_CMD_PTR_NM
            ,(SELECT CODE_NM FROM CMM_CODE_D WHERE CODE_GRP_ID = 'PRD024' AND CMM_CODE_D.CODE = PPM.PRS_SP_CD) PRS_SP_NM
            ,(SELECT Z.CODE_NM FROM CMM_CODE_D Z WHERE Z.CODE_GRP_ID = 'PRD010' AND Z.CODE = PPM.PRD_PTR_CD) AS PRD_PTR_NM
            ,FSCM.STKI_CMD_DATE
            ,FSCM.STKI_DDL_DATE
            ,FSCM.ORG_STKI_CMD_ID
            ,FSCM.STKI_CMD_REQ_ID
            ,(SELECT CODE_NM FROM CMM_CODE_D WHERE CODE_GRP_ID = 'PRD103' AND CMM_CODE_D.CODE = FSCM.STOCK_MDA_CD) AS STOCK_MDA_NM
            ,(SELECT Z.CODE_NM FROM CMM_CODE_D Z WHERE Z.CODE_GRP_ID = 'PRD014' AND Z.CODE = PPM.WH_PTR_CD) AS WH_PTR_NM
            ,NVL((SELECT SUM(STKI_CMD_QTY) STKI_CMD_QTY FROM FUL_STKI_CMD_D WHERE STKI_CMD_ID = FSCM.STKI_CMD_ID AND FSCM.WH_NO = WH_NO),0) AS STKI_CMD_QTY
            ,NVL((SELECT SUM(FSND1.STKI_CNFM_QTY)
                  FROM FUL_STKI_SO_D   FSSD1
                      ,FUL_STKI_CNFM_D FSND1
                  WHERE FSSD1.WH_NO           = FSND1.WH_NO
                    AND FSSD1.STKI_SO_ID      = FSND1.STKI_SO_ID           
                    AND FSSD1.STKI_SO_DTL_SEQ = FSND1.STKI_SO_DTL_SEQ
                    AND FSSD1.WH_NO           = FSCM.WH_NO
                    AND FSSD1.STKI_CMD_ID     = FSCM.STKI_CMD_ID),0) AS STKI_CMD_CNFM_QTY
            ,NVL((SELECT SUM(
                           CASE WHEN FSSD1.STKI_SO_PGS_STS_CD = '91' THEN 0
                                 WHEN FSSD1.STKI_SO_PGS_STS_CD = '92' THEN FSSD1.STKI_SO_ARRIV_QTY
                                ELSE FSSD1.STKI_SO_ARR_QTY  END )
                  FROM FUL_STKI_SO_D FSSD1
                 WHERE FSSD1.WH_NO         = FSCM.WH_NO
                    AND FSSD1.STKI_CMD_ID   = FSCM.STKI_CMD_ID ),0)  AS STKI_CMD_SO_ARR_QTY
            ,NVL((SELECT SUM(FSQD1.QA_FLR_QTY)
                  FROM FUL_STKI_SO_D   FSSD1
                      ,FUL_STKI_QA_D   FSQD1
                  WHERE FSSD1.WH_NO           = FSQD1.WH_NO
                    AND FSSD1.STKI_SO_ID      = FSQD1.STKI_SO_ID
                    AND FSSD1.STKI_SO_DTL_SEQ = FSQD1.STKI_SO_DTL_SEQ                  
                    AND FSSD1.WH_NO           = FSCM.WH_NO
                    AND FSSD1.STKI_CMD_ID     = FSCM.STKI_CMD_ID),0) AS STKI_CMD_QA_FLR_QTY                                       
        FROM FUL_STKI_CMD_M FSCM
            ,PRD_PRD_M PPM
       WHERE FSCM.PRD_ID = PPM.PRD_ID
         AND FSCM.DLR_ID = '803'
         AND FSCM.STKI_CMD_PGS_STS_CD IN ('16', '20', '30')                                                                            
         AND FSCM.STKI_CMD_PGS_STS_CD NOT IN ('90','95')                                                                                         
         ) A                                                                                                                                       
        ORDER BY A.STKI_CMD_DATE DESC

我看了一下SQL，又是标量子查询。。。看了下执行计划，果然是大表用了标量子查询走了嵌套循环的原因。把大表的标量子查询改成left join即可，改完执行时间1s搞定。SQL如下：

SELECT / system-mng-mapper | selectPtnDispatchbillInsert  /
 STKI_CMD_ID --采购单号
, WH_NO --仓库号
, WH_NM --仓库名
, STKI_CMD_PGS_STS_NM --执行状态
, PRD_ID --商品号
, PRD_NM --商品名
, MD_ID --MD号
, MD_NM --MD名
, STKI_CMD_PTR_NM --采购类型
, PRS_SP_NM --合作方式
, PRD_PTR_NM --商品类型
, STKI_CMD_DATE --采购指示日
, STKI_DDL_DATE --采购到期日
, ORG_STKI_CMD_ID --原采购单号
, STKI_CMD_REQ_ID --采购申请号
, STOCK_MDA_NM --库存渠道
, WH_PTR_NM --入库属性
, STKI_CMD_QTY AS STKI_CMD_QTY --采购数量
, (A.STKI_CMD_SO_ARR_QTY - A.STKI_CMD_CNFM_QTY - A.STKI_CMD_QA_FLR_QTY) AS STKI_SO_PGS_QTY --发货在途数量
, STKI_CMD_CNFM_QTY AS STKI_CNFM_QTY --入库数量            
, (A.STKI_CMD_QTY - (A.STKI_CMD_SO_ARR_QTY - A.STKI_CMD_QA_FLR_QTY)) AS STKI_RMN_QTY --剩余数量   
  FROM (SELECT PG_FUL_UTIL.FN_FUL_CMD_MASK(FSCM.STKI_CMD_ID) AS STKI_CMD_ID,
               FSCM.WH_NO,
               (SELECT WH_NM FROM FUL_WH_M WHERE FUL_WH_M.WH_NO = FSCM.WH_NO) WH_NM,
               (SELECT CODE_NM
                  FROM CMM_CODE_D
                 WHERE CODE_GRP_ID = 'FUL005'
                   AND CMM_CODE_D.CODE = FSCM.STKI_CMD_PGS_STS_CD) STKI_CMD_PGS_STS_NM,
               FSCM.PRD_ID,
               (SELECT PRD_NM
                  FROM PRD_PRD_M
                 WHERE PRD_PRD_M.PRD_ID = FSCM.PRD_ID) AS PRD_NM,
               FSCM.MD_ID,
               (SELECT MD_NM FROM PRD_MD_M WHERE PRD_MD_M.MD_ID = FSCM.MD_ID) AS MD_NM,
               (SELECT CODE_NM
                  FROM CMM_CODE_D
                 WHERE CODE_GRP_ID = 'FUL006'
                   AND CMM_CODE_D.CODE = FSCM.STKI_CMD_PTR_CD) STKI_CMD_PTR_NM,
               (SELECT CODE_NM
                  FROM CMM_CODE_D
                 WHERE CODE_GRP_ID = 'PRD024'
                   AND CMM_CODE_D.CODE = PPM.PRS_SP_CD) PRS_SP_NM,
               (SELECT Z.CODE_NM
                  FROM CMM_CODE_D Z
                 WHERE Z.CODE_GRP_ID = 'PRD010'
                   AND Z.CODE = PPM.PRD_PTR_CD) AS PRD_PTR_NM,
               FSCM.STKI_CMD_DATE,
               FSCM.STKI_DDL_DATE,
               FSCM.ORG_STKI_CMD_ID,
               FSCM.STKI_CMD_REQ_ID,
               (SELECT CODE_NM
                  FROM CMM_CODE_D
                 WHERE CODE_GRP_ID = 'PRD103'
                   AND CMM_CODE_D.CODE = FSCM.STOCK_MDA_CD) AS STOCK_MDA_NM,
               (SELECT Z.CODE_NM
                  FROM CMM_CODE_D Z
                 WHERE Z.CODE_GRP_ID = 'PRD014'
                   AND Z.CODE = PPM.WH_PTR_CD) AS WH_PTR_NM,
               NVL((SELECT SUM(STKI_CMD_QTY) STKI_CMD_QTY
                     FROM FUL_STKI_CMD_D
                    WHERE STKI_CMD_ID = FSCM.STKI_CMD_ID
                      AND FSCM.WH_NO = WH_NO),
                   0) AS STKI_CMD_QTY,
               NVL(v1.SUM_STKI_CNFM_QTY, 0) AS STKI_CMD_CNFM_QTY,
               NVL((SELECT SUM(CASE
                                WHEN FSSD1.STKI_SO_PGS_STS_CD = '91' THEN
                                 0
                                WHEN FSSD1.STKI_SO_PGS_STS_CD = '92' THEN
                                 FSSD1.STKI_SO_ARRIV_QTY
                                ELSE
                                 FSSD1.STKI_SO_ARR_QTY
                              END)
                     FROM FUL_STKI_SO_D FSSD1
                    WHERE FSSD1.WH_NO = FSCM.WH_NO
                      AND FSSD1.STKI_CMD_ID = FSCM.STKI_CMD_ID),
                   0) AS STKI_CMD_SO_ARR_QTY,
               NVL(SUM_QA_FLR_QTY, 0) AS STKI_CMD_QA_FLR_QTY
          FROM FUL_STKI_CMD_M FSCM
         inner join PRD_PRD_M PPM
            on (FSCM.PRD_ID = PPM.PRD_ID)
          left join (SELECT SUM(FSND1.STKI_CNFM_QTY) as SUM_STKI_CNFM_QTY,
                           FSSD1.WH_NO,
                           FSSD1.STKI_CMD_ID
                      FROM FUL_STKI_SO_D FSSD1, FUL_STKI_CNFM_D FSND1
                     WHERE FSSD1.WH_NO = FSND1.WH_NO
                       AND FSSD1.STKI_SO_ID = FSND1.STKI_SO_ID
                       AND FSSD1.STKI_SO_DTL_SEQ = FSND1.STKI_SO_DTL_SEQ
                     group by FSSD1.WH_NO, FSSD1.STKI_CMD_ID) V1
            on (v1.WH_NO = FSCM.WH_NO AND v1.STKI_CMD_ID = FSCM.STKI_CMD_ID)
          left join (SELECT SUM(FSQD1.QA_FLR_QTY) as SUM_QA_FLR_QTY,
                           FSSD1.WH_NO,
                           FSSD1.STKI_CMD_ID
                      FROM FUL_STKI_SO_D FSSD1, FUL_STKI_QA_D FSQD1
                     WHERE FSSD1.WH_NO = FSQD1.WH_NO
                       AND FSSD1.STKI_SO_ID = FSQD1.STKI_SO_ID
                       AND FSSD1.STKI_SO_DTL_SEQ = FSQD1.STKI_SO_DTL_SEQ
                     group by FSSD1.WH_NO, FSSD1.STKI_CMD_ID) v2
            on (v2.WH_NO = FSCM.WH_NO AND v2.STKI_CMD_ID = FSCM.STKI_CMD_ID)
         WHERE 1 = 1
           AND FSCM.DLR_ID = '803'
           AND FSCM.STKI_CMD_PGS_STS_CD IN ('16', '20', '30')
           AND FSCM.STKI_CMD_PGS_STS_CD NOT IN ('90', '95')) A
 ORDER BY A.STKI_CMD_DATE DESC

我们在写SQL的时候，记住大表的标量子查询一定要改成left join，切记~

继续阅读