94-SQL优化案例一则(用到的写法经常是被嫌弃的)

客户生产系统上的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，如何补救？> , 有的东西并不一定都是坏的, 有时我们反而可以用它来做优化.

(本篇完)