SQL文本：
SELECT *
FROM(SELECT PAGE.*, ROWNUM PAGEROWNUM
FROM (SELECT CLASS1,
CLASS2,
SPBM,
CLASS3,
MFLC,
MFSWFZR,
SUM(BQSR) BQSR,
SUM(BQML) BQML,
SUM(DBQSR) DBQSR,
SUM(DBQML) DBQML,
SUM(TQSR) TQSR,
SUM(TQML) TQML,
SUM(HBSR) HBSR,
SUM(HBML) HBML
FROM (SELECT *+ RULE +*/
CLASS1,
CLASS2,
SPBM,
CLASS3,
MFLC,
MFSWFZR,
SUM(SGLXSSR -(SGLGCERT - NVL(SGLN25, 0)) -
NVL(SGLN25, 0) +NVL(SGLN24, 0) + NVL(SGLN22, 0) +
NVL(SGLN23, 0) +NVL(SGLN21, 0) + NVL(SGLN20, 0)) BQSR,
SUM(SGLN2 - (SGLGCERT- NVL(SGLN25, 0)) -
NVL(SGLN25, 0) +NVL(SGLN24, 0) + NVL(SGLN22, 0) +
NVL(SGLN23, 0) +NVL(SGLN21, 0) + NVL(SGLN20, 0)) BQML,
0.00 DBQSR,
0.00 DBQML,
0.00 TQSR,
0.00 TQML,
0.00 HBSR,
0.00 HBML
FROM SALEGOODSLIST,VIEW_MFRAME_ALL, MANAFRAME
WHERE SGLMFID = CLASS3
AND SGLMFID = MFCODE
AND SGLHSRQ >=TO_DATE('2017-01-01', 'YYYY-MM-DD')
AND SGLHSRQ <
TO_DATE('2017-04-11', 'YYYY-MM-DD') + 1
AND SGLWMID LIKENVL('', '%')
AND CLASS1 LIKENVL('2007', '%')
AND CLASS2 LIKE NVL('200709','%')
AND SPBM LIKENVL('20070901', '%')
AND CLASS3 LIKENVL('', '%')
AND 'MFLC' = 'MFLC'
GROUP BY CLASS1,CLASS2, SPBM, CLASS3, MFLC, MFSWFZR
UNION
SELECT *+ RULE +*/
CLASS1,
CLASS2,
SPBM,
CLASS3,
MFLC,
MFSWFZR,
0.00 BQSR,
0.00 BQML,
SUM(SGLXSSR -(SGLGCERT - NVL(SGLN25, 0)) -
NVL(SGLN25, 0) +NVL(SGLN24, 0) + NVL(SGLN22, 0) +
NVL(SGLN23, 0) +NVL(SGLN21, 0) + NVL(SGLN20, 0)) DBQSR,
SUM(SGLN2 - (SGLGCERT- NVL(SGLN25, 0)) -
NVL(SGLN25, 0) +NVL(SGLN24, 0) + NVL(SGLN22, 0) +
NVL(SGLN23, 0) + NVL(SGLN21, 0) +NVL(SGLN20, 0)) DBQML,
0.00 TQSR,
0.00 TQML,
0.00 HBSR,
0.00 HBML
FROM SALEGOODSLIST,VIEW_MFRAME_ALL, MANAFRAME
WHERE SGLMFID = CLASS3
AND SGLMFID = MFCODE
AND SGLHSRQ >=TO_DATE('2016-01-01', 'YYYY-MM-DD')
AND SGLHSRQ <
TO_DATE('2017-04-11','YYYY-MM-DD') + 1
AND SGLWMID LIKENVL('', '%')
AND SGLWMID LIKENVL('', '%')
AND CLASS1 LIKENVL('2007', '%')
AND CLASS2 LIKENVL('200709', '%')
AND SPBM LIKENVL('20070901', '%')
AND CLASS3 LIKENVL('', '%')
AND 'MFLC' = 'MFLC'
GROUP BY CLASS1,CLASS2, SPBM, CLASS3, MFLC, MFSWFZR
UNION
SELECT *+ RULE +*/
CLASS1,
CLASS2,
SPBM,
CLASS3,
MFLC,
MFSWFZR,
0.00 BQSR,
0.00 BQML,
0.00 DBQSR,
0.00 DBQML,
SUM(SGLXSSR -(SGLGCERT - NVL(SGLN25, 0)) -
NVL(SGLN25, 0) +NVL(SGLN24, 0) + NVL(SGLN22, 0) +
NVL(SGLN23, 0) +NVL(SGLN21, 0) + NVL(SGLN20, 0)) TQSR,
SUM(SGLN2 - (SGLGCERT- NVL(SGLN25, 0)) -
NVL(SGLN25, 0) +NVL(SGLN24, 0) + NVL(SGLN22, 0) +
NVL(SGLN23, 0) +NVL(SGLN21, 0) + NVL(SGLN20, 0)) TQML,
0.00 HBSR,
0.00 HBML
FROM SALEGOODSLIST,VIEW_MFRAME_ALL, MANAFRAME
WHERE SGLMFID = CLASS3
AND SGLMFID = MFCODE
AND SGLHSRQ >=
ADD_MONTHS(TO_DATE('2017-01-01', 'YYYY-MM-DD'),
-12)
AND SGLHSRQ <
ADD_MONTHS(TO_DATE('2017-04-11', 'YYYY-MM-DD'),
-12)+ 1
AND SGLWMID LIKENVL('', '%')
AND SGLWMID LIKENVL('', '%')
AND CLASS1 LIKENVL('2007', '%')
AND CLASS2 LIKENVL('200709', '%')
AND SPBM LIKENVL('20070901', '%')
AND CLASS3 LIKE NVL('', '%')
AND 'MFLC' = 'MFLC'
GROUP BY CLASS1,CLASS2, SPBM, CLASS3, MFLC, MFSWFZR
UNION
SELECT /*+ RULE +*/
CLASS1,
CLASS2,
SPBM,
CLASS3,
MFLC,
MFSWFZR,
0.00 BQSR,
0.00 BQML,
0.00 DBQSR,
0.00 DBQML,
0.00 TQSR,
0.00 TQML,
SUM(SGLXSSR -(SGLGCERT - NVL(SGLN25, 0)) -
NVL(SGLN25, 0) +NVL(SGLN24, 0) + NVL(SGLN22, 0) +
NVL(SGLN23, 0) +NVL(SGLN21, 0) + NVL(SGLN20, 0)) HBSR,
SUM(SGLN2 - (SGLGCERT- NVL(SGLN25, 0)) -
NVL(SGLN25, 0) +NVL(SGLN24, 0) + NVL(SGLN22, 0) +
NVL(SGLN23, 0) +NVL(SGLN21, 0) + NVL(SGLN20, 0)) HBML
FROM SALEGOODSLIST,VIEW_MFRAME_ALL, MANAFRAME
WHERE SGLMFID = CLASS3
AND SGLMFID = MFCODE
AND SGLHSRQ >=
ADD_MONTHS(TO_DATE('2017-01-01', 'YYYY-MM-DD'),
-1)
AND SGLHSRQ <
ADD_MONTHS(TO_DATE('2017-04-11', 'YYYY-MM-DD'),
-1) +1
AND SGLWMID LIKENVL('', '%')
AND SGLWMID LIKENVL('', '%')
AND CLASS1 LIKENVL('2007', '%')
AND CLASS2 LIKENVL('200709', '%')
AND SPBM LIKENVL('20070901', '%')
AND CLASS3 LIKENVL('', '%')
AND 'MFLC' = 'MFLC'
GROUP BY CLASS1,CLASS2, SPBM, CLASS3, MFLC, MFSWFZR)
GROUP BY CLASS1, CLASS2, SPBM, CLASS3, MFLC, MFSWFZR) PAGE)
WHERETRUNC((PAGEROWNUM - 1) / 10000) = 0
;
SQL执行的相关统计信息：
如上图所示，该SQL在17天的采集时间内，共执行了1次，总执行时间为10,758秒(s)，大概为2.9小时(h)，逻辑读也很大，大概为422,788,870。可以进行优化。
优化前SQL的执行计划如下所示：
统计信息：
创建了如下索引：
CREATE INDEX DBUSRMKT.idx_SALEGOODSLIST_01 ONDBUSRMKT.SALEGOODSLIST(SGLMFID,SGLHSRQ,SGLWMID)ONLINE NOLOGGING PARALLEL 8;
ALTER INDEX DBUSRMKT.idx_SALEGOODSLIST_01NOPARALLEL;
优化后的执行计划如下：
统计信息：
优化后效果如下：
SQL ID | 优化前 | 优化后 | 提高效果 | |||
执行时间 | 逻辑读 | 执行时间 | 逻辑读 | 执行时间 | 逻辑读 | |
3q0mbn02zfg57 | 81.62秒(s) | 3562062 | 1.48秒(s) | 533221 | 55倍多 | 6倍多 |
