ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 408|回复: 2

[原创] 2018年第一个SQL优化案例

[复制链接]
论坛徽章:
9
娜美
日期:2017-06-26 15:18:15目光如炬
日期:2017-08-20 22:00:00目光如炬
日期:2017-08-27 22:00:01火眼金睛
日期:2017-09-01 17:00:07目光如炬
日期:2017-09-03 22:00:01火眼金睛
日期:2017-09-30 22:00:01目光如炬
日期:2017-11-26 22:00:00目光如炬
日期:2017-12-17 22:00:00目光如炬
日期:2018-01-14 22:00:00
跳转到指定楼层
1#
发表于 2018-1-2 16:54 | 只看该作者 |只看大图 回帖奖励 |倒序浏览 |阅读模式
目前就职海天起点,服务于电力行业,致力于帮助客户解决生产过程中出现的问题,提高生产效率, 爱好书法,周易!愿结交志同道合之士!共同进步! 微信号:sunyunyi_sun


现象:
客户反映过程中的SQL语句查询不出结果,着急出结果,SQL如下:
INSERT INTO MID_CTB_CONS_POWERCUT
      (DEPT_ID,
      DATA_MONTH,
      CONS_NO,
      SORT_CODE,
      LRPC_FLG
      )
    SELECT P.T_5_DEPT_ID,
       '201712',
       A2.CONS_NO,
       SUBSTR(A2.CONS_SORT_CODE, 1, 2),
       '0'
    FROM TB_CONS@DB_LK A2, FM04_DEPT P
    WHERE EXISTS
         (SELECT 1
          FROM TB_RCA_CTRL_DET@DB_LK
          WHERE CONS_NO = A2.CONS_NO
          AND (CTRL_TYPE LIKE '02%' OR CTRL_TYPE LIKE '03%' OR
               CTRL_TYPE = '0401')
          AND REMOTE_RSLT = '03'
          AND TO_CHAR(REMOTE_DATE,'YYYYMMDD') BETWEEN '20171201' AND '20171231'
         )
    AND A2.ORG_NO = P.S_DEPT_ID
    AND A2.ORG_NO LIKE '61%';


处理步骤:


收集相关信息:
FM04_DEPT             大概500行
TB_CONS@DB_LK         大概600万,无有效可用条件
TB_RCA_CTRL_DET@DB_LK 大概300万,REMOTE_DATE 为DATE类型有index




首先过滤条件字段存在索引但是却被函数转换需改写SQL使用索引过滤数据量:
AND TO_CHAR(REMOTE_DATE,'YYYYMMDD') BETWEEN '20171201' AND '20171231'
改写为:
REMOTE_DATE BETWEEN to_date('20171201','yyyymmdd') AND to_date('20171231','yyyymmdd')
这样可过滤因散列读引起的不必要的数据量。


然后看执行执行:


SELECT P.T_5_DEPT_ID,
       '201712',
       A2.CONS_NO,
       SUBSTR(A2.CONS_SORT_CODE, 1, 2),
       '0'
    FROM TB_CONS@DB_LK A2, FM04_DEPT P
    WHERE EXISTS
         (SELECT 1
          FROM  TB_RCA_CTRL_DET@DB_LK
          WHERE CONS_NO = A2.CONS_NO
          AND (CTRL_TYPE LIKE '02%' OR CTRL_TYPE LIKE '03%' OR
               CTRL_TYPE = '0401')
          AND REMOTE_RSLT = '03'
          AND REMOTE_DATE BETWEEN to_date('20171201','yyyymmdd') AND to_date('20171231','yyyymmdd')
         )
    AND A2.ORG_NO = P.S_DEPT_ID
    AND A2.ORG_NO LIKE '6140%'


查不出结果!!


从原始的执行计划可以看到
1:cost值很大,执行计划不理想
2:hash 中间结果数据量很大,这个结果做为驱动表和TB_RCA_CTRL_DET过滤链接非常耗时,不合理。
3:两个远程表应该首先过滤数据才是呀!这样才合理。




优化思路:让TB_RCA_CTRL_DET表作为驱动表和TB_CONS进行连接,IN 操作会让子查询作为驱动表(EXISTS 操作相反),改写如下:


SELECT P.T_5_DEPT_ID,
       '201712',
       A2.CONS_NO,
       SUBSTR(A2.CONS_SORT_CODE, 1, 2),
       '0'
    FROM TB_CONS@DB_LK A2, FM04_DEPT P
    WHERE A2.CONS_NO in
         (SELECT CONS_NO
          FROM  TB_RCA_CTRL_DET@DB_LK
          WHERE  REMOTE_RSLT = '03'
          AND REMOTE_DATE BETWEEN to_date('20171201','yyyymmdd') AND to_date('20171231','yyyymmdd')
          AND (CTRL_TYPE LIKE '02%' OR CTRL_TYPE LIKE '03%' OR
               CTRL_TYPE = '0401')
         )
    AND A2.ORG_NO = P.S_DEPT_ID
    AND A2.ORG_NO LIKE '61%'





24491 rows selected.


Elapsed: 00:00:21.95


改写后的执行计划
1:首先TB_RCA_CTRL_DET的内部视图VW_NSO_1和TB_CONS进行NESTED LOOPS,TB_RCA_CTRL_DET通过时间索引数据量很小作为驱动表,合理。
2:然后FM04_DEPT小表做驱动和远程表嵌套的结果集做hash,合理。
3: 22秒查出结果






2018-01-01
孙显鹏




求职 : 数据库管理员
认证徽章
论坛徽章:
5
沸羊羊
日期:2015-03-04 14:55:412015年新春福章
日期:2015-03-06 11:59:47美羊羊
日期:2015-06-01 19:29:53水瓶座
日期:2015-07-11 22:36:54布鲁克
日期:2017-04-12 12:45:46
2#
发表于 2018-1-12 09:19 | 只看该作者
学习了,

使用道具 举报

回复
论坛徽章:
0
3#
发表于 2018-1-12 15:08 | 只看该作者
学习了

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

DTCC2018购票6.8折优惠进行时

中国数据库技术大会是国内数据库及大数据领域规模最大、最受欢迎的技术交流盛会。 2018年5月10-12日,第九届中国数据库技术大会将如约而至。本届大会以“数领先机•智赢未来”为主题,设定2大主会场及20个技术专场,邀请来自国内外互联网、金融、教育等行业百余位技术专家,共同探讨Oracle、MySQL、NoSQL、大数据等领域的前瞻性热点话题与技术。
----------------------------------------
优惠时间:2018年2月13日前

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表
北京赛车pk10 申博官网 北京赛车pk10 六台宝典现场开奖 北京赛车pk10历史记录 北京赛车开奖 北京赛车预测 949494开奖结果今晚 威尼斯人线上娱乐 手机投注平台 幸运28投注技巧 pk10助赢软件 北京pk10百度鼎盛彩票网 北京赛车聚彩 北京pk10如何稳杀3码 pk10稳赢方法 pk10定位计划 pk10包赢计划群