【恩墨學院】走在專家的路上，每天一條SQL最佳化

SQL文字：

 

SELECT *

  FROM(SELECT PAGE.*, ROWNUM PAGEROWNUM

         FROM (SELECT *

                 FROM (SELECT CLASS1,

                               CLASS2,

                               SPBM,

                               GMFMFID GSTMFID,

                               GBID,

                               GBCNAME,

                              GBPPCODE,

                               GBSPEC,

                               GBSUPID,

                               GMFWMID,

                               GMPHSJJ PJJJ,

                               GMPSJ SJ,

                               GMPKL,

                              F_GETFIRSTSALEDATE(GBID, GBSUPID, GMFMFID) FIRSTSALEDATE,

                              F_GETLASTSALEDATE(GBID, GBSUPID, GMFMFID) LASTSALEDATE,

                               (SELECTNVL(SUM(GSTKCSL), 0)

                                  FROM GOODSSTOCK

                                 WHERE GSTGDID= GBID) GSTKCSL,

                               (SELECTNVL(SUM(GSTKCHSJJJE), 0)

                                  FROMGOODSSTOCK

                                 WHERE GSTGDID= GBID) GSTKCHSJJJE,

                               (SELECTNVL(SUM(GSTKCHSSJJE), 0)

                                  FROMGOODSSTOCK

                                 WHERE GSTGDID= GBID) GSTKCHSSJJE

                          FROM GOODSBASE,

                               GOODSMFRAME,

                               VIEW_MFRAME_ALL,

                               GOODSMFPRICE

                         WHERE GBID = GMFGDID

                           AND GMFMFID = CLASS3

                           AND GMPGDID = GBID

                           AND GMPMFID =GMFMFID

                           AND GBSTATUS = 'Y'

                           AND'GOODSBASE.GBSUPID' = 'GOODSBASE.GBSUPID'

                           ANDGOODSMFRAME.GMFWMID = '1'

                           AND 'VIEW_MFRAME_ALL.CLASS1' =

                              'VIEW_MFRAME_ALL.CLASS1'

                           AND'VIEW_MFRAME_ALL.CLASS2' =

                              'VIEW_MFRAME_ALL.CLASS2'

                           AND 'VIEW_MFRAME_ALL.SPBM'= 'VIEW_MFRAME_ALL.SPBM'

                           AND'GOODSMFRAME.GMFMFID' = 'GO ODSMFRAME.GMFMFID'

                           AND'GOODSBASE.GBPPCODE' = 'GOODSBASE.GBPPCODE'

                           AND'GOODSBASE.GBCATCODE' = 'GOODSBASE.GBCATCODE'

                           AND NOT EXISTS

                         (SELECT 'X'

                                  FROMSALEGOODSLIST

                                 WHERE SGLGDID= GBID

                                   AND SGLMFID= GMFMFID

                                   AND SGLDATE> = TRUNC(SYSDATE) - '7')

                         GROUP BY CLASS1,

                                  CLASS2,

                                  SPBM,

                                  GMFMFID,

                                  GBID,

                                  GBCNAME,

                                  GBPPCODE,

                                  GBSPEC,

                                  GBSUPID,

                                  GMFWMID,

                                  GMPHSJJ,

                                  GMPSJ,

                                  GMPKL,

                                 F_GETFIRSTSALEDATE(GBID, GMFMFID, GBSUPID),

                                 F_GETLASTSALEDATE(GBID, GMFMFID, GBSUPID))

                WHERE (GMFWMID IN ('1', '2', '3') AND GSTKCSL <> 0)

                   OR (GMFWMID IN ('4', '5') AND GSTKCSL = 0)) PAGE)

 WHERETRUNC((PAGEROWNUM - 1) / 10000) = 0

;

SQL執行的相關統計資訊：

如上圖所示，該SQL在17天的採集時間內，共執行了1次，總執行時間為16,579秒(s)，大概為4.6小時(h), 邏輯讀也很大，大概為2,786,393,641。可以進行最佳化。

最佳化前SQL的執行計劃如下所示：

建立瞭如下索引：

CREATE INDEX DBUSRMKT.idx_SALEGOODSLIST_02 ONDBUSRMKT.SALEGOODSLIST(SGLGDID,SGLMFID,SGLSUPID,SGLDATE)ONLINE NOLOGGINGPARALLEL 8;

ALTER INDEX DBUSRMKT.idx_SALEGOODSLIST_02NOPARALLEL;

最佳化後的執行計劃如下：

統計資訊如下：

最佳化後效果如下：

SQL ID	最佳化前		最佳化後		提高效果
	執行時間	邏輯讀	執行時間	邏輯讀	執行時間	邏輯讀
6d0u8h07a858u	16548秒(s)	2,786,393,641	3.06秒(s)	838881	5400多倍	3300多倍

恩墨學院隸屬於雲和恩墨（北京）資訊科技有限公司，致力於提供專業高水準的與大資料培訓服務，挖掘培養大資料與資料庫人才。恩墨學院提供包括個人實戰技能培訓、個人認證培訓、企業內訓在內的全方位大資料和資料庫技術培訓。ACE級別超強師資，配備專業實驗室，沉浸式學習與訓練，專業實驗室、配備專業助教指導訓練。能迅速融入專家圈子，業內資源豐富，迅速積累職場人脈。課程包括：班、Oracle 、Oracle OCP考試等。