2016年11月

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'

- 阅读剩余部分 -

Title - Artist
0:00