Loading [MathJax]/jax/input/TeX/config.js
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >SQL优化,需要多一些严谨的态度

SQL优化,需要多一些严谨的态度

作者头像
老虎刘
发布于 2022-06-22 09:59:47
发布于 2022-06-22 09:59:47
3120
举报

分享是一种美德,接受得了批评建议更是难得的美德。有很多大师写博客、写公众号、写书,都是一种分享,好事情.但有些人有这样一个毛病,不喜欢听别人的批评建议。公众号下面的留言是一种很好的技术交流途径,有些公众号只把吹捧的评论显示出来,批评建议留着自己"欣赏",这种行为我认为是非常可耻的,因为你宣传了错误的东西又没有更正,会误导公众。这些公众号屏蔽批评建议的做法不知是不是小编私下做主,建议请示下领导,做技术的容不下批评还做个屁啊。大师也有犯错的时候,承认错误不可耻。

在知识累积不够或是考虑不周的情况下,有时就会表现出严谨度不够、方法欠佳等问题,更有甚者还可能得出一些错误的结论。所以有时候知道的越多反而越不敢写。老虎刘非常欢迎大家公开讨论技术,而且保证全部批评建议公开.

下面就列举一些例子(有些例子是网友提供,表示感谢):

case1(来自某用户组):改写建议,严谨不足

原SQL: TO_CHAR(a.VERI_DATE,'yyyymmdd') = :B1

原改写建议:

a.VERI_DATE>truncate(sysdate-1) and a.VERI_DATE <=truncate(sysdate)

这个改写建议我相信很大程度上可能是笔误,因为原文把trunc还写成truncate了。其实等价改写应该是这样:

a.VERI_DATE>=to_date(:B1,'yyyymmdd') and a.VERI_DATE<to_date(:B1,'yyyymmdd')+1

原改写的问题关键不在于用:B1还是sysdate(应该B1取的就是前一天的日期),而是等于号在前还是在后。原改写没有把前一天的第一秒算进去,反倒是把第二天的第一秒算了进来。一秒钟可能发生很多事情.

case2(来自某用户组):改写建议,严谨不足

原SQL:TO_CHAR(remote_date,'yyyymmdd') between '20171201' and '20171231'

原改写建议:

remote_date between to_date('20171201','yyyymmdd') and to_date('20171231','yyyymmdd')

这个改写把20171231这一天只取了00:00:00有一个时间点,剩下的23小时59分59秒都丢掉了,这个改写就太不严谨了。

正确的改写应该是:

remote_date >=to_date('20171201','yyyymmdd') and remote_date <to_date('20171231','yyyymmdd')+1

case3(来自某用户组):误打误撞,问题解决,结论错误

这个案例虽然最后的优化效果堪称完美,但是对问题根源的分析却是错误的。原SQL:

Select xxx FROM cs_custinfo where vsrcrowid = :"SYS_B_0" and nvl(dr,:"SYS_B_1")=:"SYS_B_2";

vsrcrowid这个字段的选择性很好,而且存在索引,但是执行计划却使用了全表扫描。原作者把这个不使用索引的问题归结于普通索引+高聚簇因子(CLUSTERING_FACTOR)。最后建议客户将字段内容做了去重处理,改成唯一索引解决,而且由此得出的结论是唯一索引比范围索引的效率高很多。

如果这个字段不适合唯一索引怎么办呢?问题原因真的是上面分析的结果吗?

其实从原作者描述的症状来看,这个case的根本原因是因为直方图的局限性所致(我在以前的公众号文章有介绍):11g的直方图最多识别字符串的前32位(12c识别到前64位),如果字符串的前32位都相同,那么直方图就会误认为字段只有一个唯一值,当然会走全表扫描;而字段上如果创建了唯一索引,那就不会创建直方图,就避免了这个问题。 这个case的常规解决方法是使用hint强制使用索引,或者删掉vsrcrowid字段上的直方图信息。而不是把索引改成唯一索引.

case4(来自某公众号):画蛇添足

原SQL,效率差,消耗大量CPU资源:

原索引字段:(ONLINEDATE, ORGID,OPCODE, SSCODE, MOCODE);

原作者通过改写SQL,调整索引对SQL进行了优化:

第一次改写后SQL(sql开发规范基本原则,尽量不在字段上做操作):

同时修改索引字段为:(ORGID, OPCODE, SSCODE,ONLINEDATE, MOCODE);(这个组合索引是调整关键)

其实到这里已经是很完美的解决了问题(不包括最后使用result_cache),但是,接下来作者又做了进一步的改写,索引还是上面修改后的索引,SQL变简单了,同时还使用了一个index_desc的hint:

这里就有两个问题了:

1.在索引正常的时候,这个改写后的sql得到的结果确实可能和原SQL一致,为什么说可能? 因为onlinedate>=trunc(sysdate)条件没有体现出来,sql选出的最大onlinedate不一定满足>=trunc(sysdate)条件,此错误1,虽然发生概率较小,影响也不大,得到的结果可能也满足生产需求.

2.在索引不可用时(invisible或是被drop的情况下),sql的结果就完全不对了(会随机找一条满足条件的记录),没有索引就得到的错误结果,那完全是不可接受的事情.

二次改写后的sql比第一次改写没有任何的性能提升,这种做法就有点画蛇添足了.

类似的不严谨文章还有很多:改写不等价,得出错误结论,优化不到位等.SQL改写需谨慎,得出结论需严谨.

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2018-09-20,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 老虎刘谈oracle性能优化 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
一条SQL引发的“血案”:与SQL优化相关的4个案例
导读:笔者早年间从事了多年开发工作,后因个人兴趣转做数据库。在长期的工作实践中,看到了数据库工作(特别是SQL优化)面临的种种问题。本文通过几个案例探讨一下SQL优化的相关问题。
用户5548425
2020/08/18
6410
一条SQL引发的“血案”:与SQL优化相关的4个案例
一线运维 DBA 五年经验常用 SQL 大全(二)
本文 SQL 及相关命令均是在运维工作中总结整理而成的,对于运维 DBA 来说可提高很大的工作效率,值得收藏。当然如果你全部能够背下来那就很牛逼了,如果不能,还是建议收藏下来慢慢看,每条 SQL 的使用频率都很高,肯定能够帮助到你。
JiekeXu之路
2021/03/15
9100
一线运维 DBA 五年经验常用 SQL 大全(二)
67-oracle数据库,有索引,但是没有被使用的N种情况,以及应对方法(上篇)
如果说性能优化是数据库技术中的明珠,那么索引无疑是其中最耀眼的一颗,特别是OLTP业务数据库。掌握了索引技术,基本上性能就不会有太大的问题。
老虎刘
2022/06/22
1.1K0
一个简单的sql审核案例 (r8笔记第90天)
今天开发的同学发来一封邮件,希望我帮忙对一个sql语句做一个评估。他们也着急要用,但是为了稳妥起见,还是希望我来审核一下,这是一个好的习惯。 打开邮件,看到的语句是下面这样的形式。 select a.cout1+b.cout2 from (select count(*) as cout1 from TEST_ONLINE where CN='' and to_char(LOGIN_TIME,'yyyymmdd') = to_char(sysdate,'yyyymmdd') and rownum = 1)
jeanron100
2018/03/19
7310
一线运维 DBA 五年经验常用 SQL 大全(三)
本文作为常用 SQL 系列的第三篇,本文涉及到的 SQL 及相关命令均是在运维工作中总结整理而成的,对于运维 DBA 来说可提高很大的工作效率,值得收藏下来慢慢看。
JiekeXu之路
2023/02/24
1.4K0
一线运维 DBA 五年经验常用 SQL 大全(三)
78-生产系统不改代码解决SQL性能问题的几种方法
数据库性能很大一部分跟SQL写法有关, 有些SQL是必须改代码才能优化的, 如果不能改代码, 需要删历史记录,回收空间,让表始终保持相对较小,才能消耗较少的资源,得到能够接受的响应时间.如前公众号文章所述: 必须通过改写SQL才能提升性能的一些情况 (列举了10个需要改写的SQL).
老虎刘
2022/06/22
4720
78-生产系统不改代码解决SQL性能问题的几种方法
SQL优化
例:select *from tempagreement where rownum<10;
一觉睡到小时候
2019/07/02
9830
数据裁剪偶遇【enq: TX - index contention】
每月将对数据库超大表进行数据裁剪,原理是通过 alter table <table_name> truncate partition (partition_name) update indexes 命令在truncate 分区的同时对索引进行更新。生产经验告诉我:“不加update indexes 会导致全局索引失效”。   裁剪任务每月凌晨自动执行,今天正好是裁剪任务执行完成,开发反馈:“裁剪任务期间订单失败率偏高”,根据开发提供的时间段查看执行日志,发现当时正是在裁剪:AATD_DTL(脱敏处理)表。
布衣530
2025/01/11
1580
数据裁剪偶遇【enq: TX - index contention】
案例纠正一则
《通过索引提升SQL性能案例一则》提到的案例,处理不太准确,有必要纠正下,更要谢谢老虎刘老师的耐心指正。
bisal
2021/03/23
3790
复杂SQL性能优化的剖析(二)(r11笔记第37天)
昨天的一篇文章复杂SQL性能优化的剖析(一)(r11笔记第36天) 分析了一个SQL语句导致的性能问题,问题也算暂时告一段落,因为这个语句的执行频率是10分钟左右,所以优化后(大概是2秒左右,需要下周再次确认)的提升很大。 对于优化是一个持续的改进,我们碰到的问题,最终的原因可能五花八门,但是正如柯南所说,真相只有一个。我把这个问题和前几天处理的一个问题结合起来,前几天处理了一个紧急问题,也是有一个SQL语句的执行计划发生改变,这个语句的业务比较关键,触发频率是每分钟一次,如果一旦出现延迟,就
jeanron100
2018/03/21
7820
复杂SQL性能优化的剖析(二)(r11笔记第37天)
105-跟专家学习SQL优化-1
网上有很多SQL优化的案例, 我本人对这方面特别感兴趣,今天就带着大家一起来学习一下专家是如何优化SQL的.
老虎刘
2023/09/01
2460
105-跟专家学习SQL优化-1
SQL优化案例-改变那些CBO无能为力的执行计划
用户写的sql,Oracle会进行等价改写,即使是RBO优化模式,Oracle也会给你做一些转换,这些转化都是基于一种固定的算法,oracle称这种转换是“启发式”的。比如我们写inner join时,并且只访问单表数据,Oracle会自动降为半连接,然后用semi join的方式给你做join。transformation是Oracle必做的一个步骤,至少在8.05版本之后transformation都一直存在。
沃趣科技
2018/05/15
1.1K0
like等价改写substr优化案例一则
客户有一套5节点的RAC系统,配置比较高,类似exadata的配置,业务量非常大,CPU使用率经常在90%以上,其中一个CPU TOP 1 SQL,占了总CPU资源将近10%。原SQL稍复杂,有UNION ALL,还有row_number分析函数,这里做了简化,只抽取了其中核心部分.
老虎刘
2022/06/22
4440
性能优化, 关键还是在SQL
很多系统上线后, 性能问题开发就基本上不管了 , 业务越来越慢的责任都压在DBA身上,而大部分DBA对SQL优化没有深入的研究, 就只能把希望寄托在硬件的改善上.
老虎刘
2022/06/27
3960
性能优化, 关键还是在SQL
一条SQL引发的“血案”:
导读:笔者早年间从事了多年开发工作,后因个人兴趣转做数据库。在长期的工作实践中,看到了数据库工作(特别是SQL优化)面临的种种问题。本文通过几个案例探讨一下SQL优化的相关问题。
朱小五
2020/08/21
7070
一条SQL引发的“血案”:
Oracle SQL调优系列之表设计与性能
在看《收获,不止sql优化》一书,并做了笔记,本博客介绍一下一些和调优相关的表比如分区表、临时表、索引组织表、簇表以及表压缩技术
SmileNicky
2022/05/07
4740
一道SQL问题,你来试试的?
如何通过SQL获取每个company最靠近年初的一组A1和A2的product数据?
bisal
2020/08/05
5940
一道SQL问题,你来试试的?
【DB笔试面试570】在Oracle中,SQL优化在写法上有哪些常用的方法?
一般在书写SQL时需要注意哪些问题,如何书写可以提高查询的效率呢?可以从以下几个方面去考虑:
AiDBA宝典
2019/09/29
3.8K0
第61篇-必须通过改写SQL才能提升性能的一些情况
这篇文章介绍了一些需要通过改写才能提高性能的SQL写法,也是对本人以前公众号改写相关文章的一个总结(也有新内容)。同时也对网络上流传的一些不太准确的说法给予纠正。改写的首要任务是等价,其次才是性能的提高,不等价的改写危害更大。
老虎刘
2022/06/22
5040
第61篇-必须通过改写SQL才能提升性能的一些情况
SQL优化:紧急情况下提高SQL性能竟是这样实现的!
作者 | 黄堋 ,多年一线 Oracle DBA 经验,长期服务电信、电网、医院、政府等行业客户。擅长数据库优化、数据库迁移升级、数据库故障处理。
数据和云
2018/07/27
4320
SQL优化:紧急情况下提高SQL性能竟是这样实现的!
推荐阅读
相关推荐
一条SQL引发的“血案”:与SQL优化相关的4个案例
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档