好久没写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大概看了一下,发现PC那段可以改写,然后做了如下修改:
SELECT A.PRD_ID,
A.UNT_SEQ,
D.BARCODE UNIT_CODE,
D.UNT_PRD_ID UNT_PRD_ID,
first_value(A.SL_PC) over(partition by A.PRD_ID, A.UNT_SEQ, D.BARCODE, D.UNT_PRD_ID order by a.PC_SEQ desc) SL_PC,
first_value(A.PRS_PC) over(partition by A.PRD_ID, A.UNT_SEQ, D.BARCODE, D.UNT_PRD_ID order by a.PC_SEQ desc) PRS_PC
FROM CHGSHS.PRD_UNT_PRD_PC_CHG_D A,
chgshs.prd_unt_prd_d d,
CHGSHS.PRD_PRD_M B,
CHGSHS.PRD_UNT_PRD_D C
where d.prd_id = a.prd_id
and d.unt_seq = a.unt_seq
and 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;
然后让同事拿去验证下数据是否一致,如果没问题就继续改写;不一会同事就说没问题了,原先4分多没出结果,现在几乎秒出结果,不用往下改了。
这种根据自己表的某一列取最大值相对应的数据时,就可以改成first_value() over()来处理,极大的加快了数据处理的时间,oracle在分析函数方面还是非常不错的。
继续阅读
您可以选择一种方式赞助本站
支付宝扫一扫赞助
微信钱包扫描赞助
赏