都是标量子查询惹的祸

都是标量子查询惹的祸

系统又报了一个跑的慢的sql语句，看图就知道这个很恐怖的，已经跑了1天了，还需要跑6个月的时间，

 

把sql语句拿出来瞅瞅：

--201406 XX

select SUM(UNPOSTING_AMT_CYC) 分期未结清余额,

SUM(greatest(nvl(BAL_TOTAL,0),0)) 余额,COUNT(1),DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end 额度区间,

CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

else '非小企业主' end 小企业主标识

from riskpubstrategy.lwt_ambs_cc_201406 A

group by DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end,

CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

else '非小企业主' end;

 

内存中的执行计划：SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('a50xqp45uy256',0,'advanced'));

简单分析下，查询的只有一个表，这就很奇怪了，不管这个表多么大，也不可能跑这么久的吧，但是有一个标量子查询，根据经验，如果标量子查询的表数据量很多，或者关联的列没有索引的话，标量子查询的性能将会是非常差的，我们由执行计划也可以看出标量子查询的表RISKPUBSC.MICRO_BUSI_DATABASE的关联列没有索引，至此，我们猜测可能是由于这个标量子查询引起sql语句性能低下，猜测归猜测，那我们实验一下呢？

 

先去掉标量子查询，然后执行一下，如下：

--201406 XX

select SUM(UNPOSTING_AMT_CYC) 分期未结清余额,

SUM(greatest(nvl(BAL_TOTAL,0),0)) 余额,COUNT(1),DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end 额度区间/*,

CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

else '非小企业主' end 小企业主标识*/

from riskpubstrategy.lwt_ambs_cc_201406 A

group by DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end/*,

CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

else '非小企业主' end*/

 

执行一下： ，发现这个sql很快的，大约274秒，也就是5分钟左右，好吧，看来真是标量子查询惹的祸，，，，，，那我们首先建立索引看看性能如何呢？

create index ind_MICRO_BUSI_DATABASE_acct on RISKPUBSC.MICRO_BUSI_DATABASE(acct) NOLOGGING parallel 8;

alter index ind_MICRO_BUSI_DATABASE_acct NOPARALLEL;

 

然后再重新执行一下之前的语句：

--201406 XX

select SUM(UNPOSTING_AMT_CYC) 分期未结清余额,

SUM(greatest(nvl(BAL_TOTAL,0),0)) 余额,COUNT(1),DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end 额度区间,

CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

else '非小企业主' end 小企业主标识

from riskpubstrategy.lwt_ambs_cc_201406 A

group by DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end,

CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

else '非小企业主' end;

加了索引后其执行计划：

 

发现性能比较好，，大约就8分钟搞定，，，，，，至此其实这个sql加个索引就算优化好了，但是根据经验，如果标量子查询的表的数据量超大（一般认为超过100W，不是绝对的）的时候，而且关联的列又不是主键的时候，标量子查询的性能其实不是怎么好，，，，，，好吧，那我们来看看他们的数据量如何呢？？？？？

 

首先看看数据量，

SELECT v.TABLE_NAME,

v.BLOCKS,

v.table_size2,

v.NUM_ROWS,

v.LAST_ANALYZED

FROM vw_table_lhr v

WHERE v.TABLE_NAME IN ('LWT_AMBS_CC_201406',

'MICRO_BUSI_DATABASE');

发现标量子查询的表是有点大，300W的数据量，如果是标量子查询的话，大约要对标量子查询的表（MICRO_BUSI_DATABASE）扫描1988W次，相当于NL连接了，所以这个性能不怎么好，那么就修改为左外连接呗，修改后的sql：

--201406 XX

select SUM(UNPOSTING_AMT_CYC) 分期未结清余额,

SUM(greatest(nvl(BAL_TOTAL,0),0)) 余额,COUNT(1),DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end 额度区间,

CASE WHEN b.ACCT is not null THEN '小企业主'

else '非小企业主' end 小企业主标识

from riskpubstrategy.lwt_ambs_cc_201406 A

LEFT outer join RISKPUBSC.MICRO_BUSI_DATABASE B

on (a.ACCT=b.ACCT)

group by DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end,

CASE WHEN b.ACCT is not null THEN '小企业主'

else '非小企业主' end

 

执行计划：

 

 

时间呢？？？？？尼玛，，，，，，，这么快，，，，，由6个月转换为44秒，，，，，，又是由自行车的速度到飞船的速度呀，，，，，，飞跃吧，，，，，，，，

这里细心的人会发现，我去掉了b.STATUS_CURR is not null 这个条件，因为我查询过这个列本身就没有为空的数据，，，，，这个又是个小小的优化哟，，，，，

 

 

最后简单总结下：

标量子查询的使用是有条件的：

①标量子查询的涉及的表的关联列必须要有索引或者关联列是主键列

②标量子查询的涉及的表的数据量不能太大，数据量太大即使关联列有索引依然很慢的

③对同一个表不能有多个标量子查询，这样性能也不会很高

 

第三个注意事项是神马意思？？？？？？？简单举个例子：

Select (select a from taba t1 where t1.id=t.id) a,

(select b from taba t1 where t1.id=t.id) b,

(select c from taba t1 where t1.id=t.id) c

……….

From tabb ;

 

这种情况大家还是修改为左外连接比较快。。。。。。如果taba稍微大点的话就会导致到大表的多次扫描，这个严重影响性能。。。。。。。。。

时间： 2024-02-28 13:15:06

都是标量子查询惹的祸的相关文章

SQL为王：oracle标量子查询和表连接改写

小鱼(邓秋爽) 云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化.SQL优化和troubleshooting 编辑手记:如何提高数据的查询效率是每个人都关注的问题,今天让我们来学习如何合理使用标量子查询和表连接方式来提高查询速度吧~ 之前小鱼就听过了标量子查询,不过对于其中的细节理解还是远远不够,借助一部分资料和自己测试对标量子查询做一点简单的分析和介绍. Oracle允许在select子句中包含单行子查询,这个也就是oracle的标量子查询,标量子查询有点类似于外

性能为王：SQL标量子查询的优化案例分析

黄廷忠(网名:认真就输) 云和恩墨技术专家 个人博客:http://www.htz.pw/ 本篇整理内容是黄廷忠在"云和恩墨大讲堂"微信分享中的讲解案例,SQL优化及SQL审核,是从源头解决性能问题的根本手段,无论是开发人员还是DBA,都应当持续深入的学习SQL开发技能,从而为解决性能问题打下根基. 本篇为系列案例之一:标量子查询优化   以下案例来自于某省电信系统EDW性能优化实践,数据库版本为11.2.0.3,运行在ORACLE Exadata一体机上,是个典型的OLAP环境,表上

[20150727]使用标量子查询小问题.txt

[20150727]使用标量子查询小问题.txt --最近一段时间一直在做优化,仔细看我前面的blog,不主张使用标量子查询,实际上还是有一些小细节要注意. 1.测试环境: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ----------------------------------------------

在MySQL中使用子查询和标量子查询的基本操作教程_Mysql

MySQL 子查询子查询是将一个 SELECT 语句的查询结果作为中间结果,供另一个 SQL 语句调用.MySQL 支持 SQL 标准要求的所有子查询格式和操作,也扩展了特有的几种特性. 子查询没有固定的语法,一个子查询的例子如下: SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1) 对应的两个数据表如下: article 文章表: user 用户表: 查询返回结果如下所示: 在该例子中,首先通过子查询语

[20140125]关于标量子查询.txt

  最近一致在优化一个垃圾项目,我发现一个奇怪的想象,就是开发很喜欢使用标量子查询,我发现这个东西像传染病一样,一个人使用其 他人也跟着仿效,而不考虑具体的使用场合.还有一些出现在视图里面. 我想通过一些例子来说明情况: @ver BANNER                                                                               CON_ID ------------------------------------------

为什么程序员都是夜猫子 电脑屏幕惹的祸？

一种很流行的说法是,程序员是把咖啡因转化成程序代码的机器. 说的是实情,随便问一个程序员,问他什么时候工作最有状态,估计他很有可能说是深夜.有人稍微早一点,有人更晚.有一种流行的趋势是凌晨4点起床,在破晓之前这段时间里做一些事情.而另一些人喜欢凌晨4点才睡觉. 所有这些的主要目的是躲避打搅.但是你把自己反锁在屋里不就行了?为什么对夜晚情有独钟? 我想,这事归纳下来有3点:工人的时间表,疲倦的大脑和明亮的电脑屏幕. 工人的时间表 Paul Graham 在2009年写了一篇关于 工人的时间表的文章

都是Javascript的作用域惹得祸

案件重现 今天有位然之OA系统的定制开发用户咨询了个问题,他想在新加的功能模块的操作面板中,实现用户点击删除按钮时提示友好提醒,如下: 问题很简单,虽然他自己最终达到目的效果了,但不知道起初问题出在哪里.通过交流了解,他开始是这么做的,大致问题代码如下: <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <script> windo

都是心太急惹的祸彩民险把10万当100元兑

刮彩票中奖了,但奖金数字才刮出"￥100"就兴奋地拿给销售员兑奖了,要不是体彩销售员讲诚信,刮开全部数字告知是"￥100000",10万元大奖可就落入别人口袋了.7月21日,前来顺德体彩中心兑取"五倍幸运"10万大奖的王先生,一个劲夸体彩销售人员诚信. 10元换来10万元大奖 7月21日,顺德伦教22107体彩网点传出刮中10万元顶呱刮大奖的喜讯.当天下午5点15分左右,三个小伙子走进顺德体彩中心兑奖大厅,其中一位拿出一张"五倍幸运&q

都是频繁换手惹的祸泰达宏利成长迎来“末位”烦恼

文/李英 向莹 来自海通证券基金研究中心的最新数据显示,基金2011年换手率整体有所下降,但仍保持平均半年换手一次的较高记录.据笔者了解,相较美国股票型基金经理平均每年60%-80%左右的换手率,中国基金经理在该指标上超过美国同业的3倍有余. 据统计,428只主动管理型偏股基金2011年的整体平均换手率为214.04%,仅低于2008年,自2009年以来连续3年下降.其中,401只开放式基金的平均换手率为215.51%,较去年降低了13.6%. 笔者认为,基金换手率水平下降与A股连续下跌无机会密