【恩墨学院】走在专家的路上，每天一条SQL优化

【恩墨学院】走在专家的路上，每天一条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多倍

恩墨学院隶属于云和恩墨（北京）信息技术有限公司，致力于提供专业高水准的oracle数据库与大数据培训服务，挖掘培养大数据与数据库人才。恩墨学院提供包括个人实战技能培训、个人认证培训、企业内训在内的全方位大数据和数据库技术培训。ACE级别超强师资，配备专业实验室，沉浸式学习与训练，专业实验室、配备专业助教指导训练。能迅速融入专家圈子，业内资源丰富，迅速积累职场人脉。oracle数据库课程包括：Oracle DBA实战班、Oracle OCM考试、Oracle OCP考试等。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28530558/viewspace-2150390/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28530558/viewspace-2150390/