客户生产系统上的SQL, 表越来越大, 执行时间越来越长, 不过只要能跑出结果, 只要不是慢到无法接受, 用户基本上都忍了.
很多客户的系统都是这样, 业务SQL消耗了过多的系统资源, 执行效率还很差, 大部分都有很大的优化空间, 只是很多人首先会想到更换高级硬件, 不知道优化才是正道(有个客户的EBS业务执行几个小时后报ora-01555错误, 硬件已经很高级了, SQL优化是唯一出路)
SQL代码(已经过简化脱敏处理):
select a.*, b.INPTBR as inst_no
from pa_agency a
left join
(
select tc.inptbr, tc.fragid
from book1 tc
union
select tcs.inptbr, tcs.fragid
from book2 tcs
) b
on a.agenid = b.fragid;
执行计划(执行时间38秒):
已知b结果集中fragid没有重复值. 3个表的记录数都显示在上图的执行计划中.请思考一下这个SQL该如何提高执行效率.
思考时间....................................................
我把这个SQL作为练习题放到了学员微信群给大家练手, 有学员已经给出了一个比较好的优化方法, 下面分别把学员的方法和我的方法列出来:
首先都是要分别创建book1和boo2两表fragid字段上的索引.
其次是需要对sql进行改写.
学员的改写方法:
select a.*,
nvl((select inptbr
from book1 tc
where a.agenid = tc.fragid
and rownum = 1
),
(select inptbr
from book2 tcs
where a.agenid = tcs.fragid
and rownum = 1
)) as inptbr
from pa_agency a;
我的改写方法:
select a.*,
(select inptbr from
(
select tc.inptbr,tc.fragid
from book1 tc
union all
select tcs.inptbr,tcs.fragid
from book2 tcs
) b where a.agenid=b.fragid
and rownum =1
) as inst_no
from pa_agency a ;
两种方法都可以大幅提升sql执行效率(预计一秒内可以执行完), 两种方法的效率也有一些细微差别, 不知道你能不能看出来?
在我的之前的这篇公众号文章中, 也有类似的优化思路 : <74-这类SQL优化，oracle输给了mysql，如何补救？> , 有的东西并不一定都是坏的, 有时我们反而可以用它来做优化.
(本篇完)
