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

 手机号登录  找回密码
 注册
查看: 4283|回复: 7

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

[复制链接]
论坛徽章:
11
目光如炬
日期:2018-01-14 22:00:00火眼金睛
日期:2018-02-28 22:00:00目光如炬
日期:2017-12-17 22:00:00目光如炬
日期:2017-11-26 22:00:00火眼金睛
日期:2017-09-30 22:00:01目光如炬
日期:2017-09-03 22:00:01火眼金睛
日期:2017-09-01 17:00:07目光如炬
日期:2017-08-27 22:00:01目光如炬
日期:2017-08-20 22:00:00娜美
日期:2017-06-26 15:18:15
发表于 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%'

01.png
查不出结果!!


从原始的执行计划可以看到
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%'


02.png


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
发表于 2018-1-12 09:19 | 显示全部楼层
学习了,

使用道具 举报

回复
论坛徽章:
0
发表于 2018-1-12 15:08 | 显示全部楼层
学习了

使用道具 举报

回复
论坛徽章:
0
发表于 2018-2-9 17:29 | 显示全部楼层
学习了

使用道具 举报

回复
认证徽章
论坛徽章:
0
发表于 2018-2-12 11:26 | 显示全部楼层
拜读了

使用道具 举报

回复
认证徽章
论坛徽章:
22
ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:00天枰座
日期:2016-01-18 10:58:39马上加薪
日期:2014-10-21 18:48:25马上加薪
日期:2014-10-21 18:48:312015年新春福章
日期:2015-03-04 14:53:162015年新春福章
日期:2015-03-06 11:58:39沸羊羊
日期:2015-06-11 17:08:14巨蟹座
日期:2015-07-10 09:11:44秀才
日期:2016-02-18 10:08:14秀才
日期:2016-06-23 14:15:06
发表于 2018-2-27 16:43 | 显示全部楼层
大数据量的查询,涉及到dblink,就要用hint固定成hash join,因为dblink这种很容易变成nestloop的算法

使用道具 举报

回复
论坛徽章:
0
发表于 2018-3-5 13:45 | 显示全部楼层
学习了

使用道具 举报

回复
论坛徽章:
3
奥运会纪念徽章:帆船
日期:2012-09-03 17:33:58ITPUB 11周年纪念徽章
日期:2012-10-09 18:06:202013年新春福章
日期:2013-02-25 14:51:24
发表于 2018-3-6 15:37 | 显示全部楼层
不错,谢谢分享!!!!!!!!!!!

使用道具 举报

回复

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

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表