分类 SQL 下的文章

SQL优化之first_value

好久没写blog了,最近又忙着学java,然后就更没有时间了。刚好今天同事有个sql需要优化,我就看了一下,然后做了一个记录。

原sql如下:

SELECT M.TORD_ID,
       '101',
       M.BIZ_UNT_CD,
       DECODE(M.BIZ_UNT_CD, '1001', '2001', '5001', '52001') L_chan_id,
       (SELECT PRD.DLR_ID
          FROM shunzi.PRD_PRD_M PRD, shunzi.PRD_UNT_PRD_D UNT
         WHERE UNT.PRD_ID = PRD.PRD_ID
           AND UNT.UNT_PRD_ID = M.UNIT_SEQ) DLR_ID,
       (SELECT UNT.PRD_ID
          FROM shunzi.PRD_UNT_PRD_D UNT
         WHERE 1 = 1
           AND UNT.UNT_PRD_ID = M.UNIT_SEQ) PRD_ID,
       M.UNIT_SEQ,
       M.BARCODE UNIT_CODE,
       NVL((SELECT RGN.STKO_WH_NO
             FROM shunzi.CMM_ZIP_NO_RGN_WH_D RGN
            WHERE RGN.WH_PTR_CD = '10'
              AND RGN.MDA_CD = '10'
              AND RGN.BIZ_UNT_CD = M.BIZ_UNT_CD
              AND RGN.LRGN_ID = M.ACC_PRV_ID
              AND RGN.MRGN_ID = M.ACC_CITY_ID
              AND RGN.SRGN_ID = M.ACC_CONTR_ID
              AND ROWNUM = 1),
           'C01') WH_CD,
       DECODE(NVL((SELECT RGN.STKO_WH_NO
                    FROM shunzi.CMM_ZIP_NO_RGN_WH_D RGN
                   WHERE RGN.WH_PTR_CD = '10'
                     AND RGN.MDA_CD = '10'
                     AND RGN.BIZ_UNT_CD = M.BIZ_UNT_CD
                     AND RGN.LRGN_ID = M.ACC_PRV_ID
                     AND RGN.MRGN_ID = M.ACC_CITY_ID
                     AND RGN.SRGN_ID = M.ACC_CONTR_ID
                     AND ROWNUM = 1),
                  'C01'),
              'C01',
              1,
              'C03',
              8,
              'C04',
              3,
              'C06',
              55,
              'C07',
              60,
              'C05',
              48,
              1) DLV_ID,
       0,
       PC.SL_PC sl_pc,
       PC.SL_PC acc_sls_amt,
       PC.PRS_PC prs_pc,
       1,
       20161124,
       M.FD_DATE,
       M.CUSER_ID,
       M.FD_DATE,
       M.CUSER_ID,
       M.FD_DATE
  FROM shunzi.PRD_TICK_ORD_M M,
       (SELECT A.PRD_ID,
               A.UNT_SEQ,
               (SELECT D.BARCODE
                  FROM shunzi.PRD_UNT_PRD_D D
                 WHERE D.PRD_ID = A.PRD_ID
                   AND D.UNT_SEQ = A.UNT_SEQ) UNIT_CODE,
               (SELECT D.UNT_PRD_ID
                  FROM shunzi.PRD_UNT_PRD_D D
                 WHERE D.PRD_ID = A.PRD_ID
                   AND D.UNT_SEQ = A.UNT_SEQ) UNT_PRD_ID,
               A.SL_PC SL_PC,
               A.PRS_PC PRS_PC
          FROM shunzi.PRD_UNT_PRD_PC_CHG_D A
         WHERE (PRD_ID, UNT_SEQ, PC_SEQ) IN
               (SELECT A.PRD_ID, A.UNT_SEQ, MAX(PC_SEQ)
                  FROM shunzi.PRD_UNT_PRD_PC_CHG_D A,
                       shunzi.PRD_PRD_M            B,
                       shunzi.PRD_UNT_PRD_D        C
                 WHERE B.PRD_ID = A.PRD_ID
                   AND B.PRD_ID = C.PRD_ID
                   AND A.UNT_SEQ = C.UNT_SEQ
                   AND C.UNT_PRD_SL_STS_CD != '10'
                   AND ((B.PRD_APP_STS_CD = '210' AND A.PC_APP_STS_CD = '250') OR
                       (B.PRD_APP_STS_CD != '210'))
                   AND SYSDATE BETWEEN A.PC_BGN_DATE AND A.PC_CL_DATE
                --AND B.PRD_PTR_CD = '50' 
                 GROUP BY A.PRD_ID, A.UNT_SEQ)) PC
 WHERE 1 = 1
   AND M.UNIT_SEQ = PC.UNT_PRD_ID
   AND M.TICKET_TYPE = '30'
   AND M.FD_DATE BETWEEN TO_DATE('2016-11-01', 'yyyy-mm-dd hh24:mi:ss') AND
       TO_DATE('2016-11-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - 0.001
   AND NVL((SELECT 1
             FROM shunzi.ORD_ORD_DTL_D G
            WHERE G.ORD_ID = M.ORD_ID
              AND G.ORD_SEQ = M.ORD_SEQ
              AND G.UNT_PRD_ID = M.UNIT_SEQ
              and (((G.ord_sts_cd = '70' and G.ord_supp_sts_cd is null) or
                  G.ord_sts_cd = '96') and G.rjt_rtn_yn = 'N')),
           '0') = '0'

- 阅读剩余部分 -

记一次标量子查询的优化

今天一个同事扔来一条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之rank/dense_rank/row_number差异

SQL之rank()、dense_rank()、row_number()的差异

--rank() dense_rank() row_number()
with xx as
 (select '1' testid, 'A1' testname, '20090520' creat_time
    from dual
  union all
  select '2' testid, 'A2' testname, '20090521' creat_time
    from dual
  union all
  select '2' testid, 'A3' testname, '20090522' creat_time
    from dual
  union all
  select '2' testid, 'A5' testname, '20090522' creat_time
    from dual
  union all
  select '3' testid, 'A4' testname, '20090523' creat_time
    from dual)
select xx.*,
       rank() over(order by xx.testid) rank,
       dense_rank() over(order by xx.testid) dense_rank,
       row_number() over(order by xx.testid) row_number
  from xx order by testid;
Title - Artist
0:00