最近在学习教主的SQL查询优化和改写那本书，作为一个小白的我，每次学习都是诚惶诚恐，不过还是有心想实战来深入学习一下，然而，公司的生产真是让我“舒心”，最近老出现慢SQL的情况，并且相当明显。所以......
操起小牛刀，上去历练一番。
征途开始：
环境：阿里云EC2服务器/oracle 11.2.0.1
一天下午2点多，发现alert告警，看到一条SQL执行报01555错，具体如下所示：
执行超过1831s，才执行完，结果查出来一共也就需要9w多数据，为何如此的慢
之后同事在一些表上添加了索引，勉强日终可以跑过去，但是还是比较慢
CREATE INDEX IDX_F_ACCINVESTBILL_01 ON F_ACCINVESTBILL("MANAGERID", "ACTUALPAYDATE");CREATE INDEX IDX_F_ACCPLANCOLLECTRECEIPT_01 ON F_ACCPLANCOLLECTRECEIPT("AUDITDATE");
然后手动执行了一下这条添加过索引的SQL语句
select u.usercode, u.username, u.orgid, u.suborgid teamid, v.orgcode apporgcode, u.id userid, p.PROTYPE, p.PROCODE PRODUCTCODE, p.PRONAME PRODUCTNAME, nvl((select sum(b1.lendamt) from f_investapplyinfo a1, f_accplancollectreceipt b1 where b1.mainapplyid = a1.id and a1.MANAGERID = u.id and b1.AUDITDATE between (select case when trunc(t.BUSINESSDATE, 'd') - 6 > trunc(t.BUSINESSDATE, 'mm') then trunc(t.BUSINESSDATE, 'd') - 6 else trunc(t.BUSINESSDATE, 'mm') end begindate from f_vsmsysdate t) and d.BUSINESSDATE and b1.proid = f.proid), 0) AchieveAmt, (select count(1) FROM F_accinvestbill a where a.ACTUALPAYDATE between (select case when trunc(t.BUSINESSDATE, 'd') - 6 > trunc(t.BUSINESSDATE, 'mm') then trunc(t.BUSINESSDATE, 'd') - 6 else trunc(t.BUSINESSDATE, 'mm') end begindate from f_vsmsysdate t) and d.BUSINESSDATE and a.proid = f.proid and u.id = a.managerid) signCount from f_vsmuser u, F_SMROLE s, F_SMUSERROLE r, f_vsmorg v, f_vsmsysdate d, f_smuserpro f, f_vsmproduct p where u.id = r.userid and u.id = f.userid and f.proid = p.ID and r.roleid = s.id and s.rolecode = 'FX02' and v.id = u.orgid and u.validflag in ('0', '1') and r.useflag = '1' and p.PROTYPE in ('00', '01', '06')
执行计划如下：
由于篇幅问题，这里不再详细贴出原计划。
可以看到执行 了143s，逻辑读106734311也就是1亿多逻辑读。
可见虽然添加索引优化以后，从1831s降到了143s， alert也不再 报01555错，但是资源消耗可以看到很高。
然后先尝试进行将标量子查询挪下来进行改写。改写SQL如下：
select u.usercode, u.username, u.orgid, u.suborgid teamid, v.orgcode apporgcode, u.id userid, p.PROTYPE, p.PROCODE PRODUCTCODE, p.PRONAME PRODUCTNAME, nvl(x.sum_leadamt, 0) AchieveAmt, signCount from f_vsmuser u, F_SMROLE s, F_SMUSERROLE r, f_vsmorg v, f_vsmsysdate d, f_smuserpro f, f_vsmproduct p, (select a1.MANAGERID, b1.proid, sum(b1.lendamt) sum_leadamt from f_investapplyinfo a1, f_accplancollectreceipt b1,f_vsmsysdate d where b1.mainapplyid = a1.id and b1.AUDITDATE between (select case when trunc(t.BUSINESSDATE, 'd') - 6 > trunc(t.BUSINESSDATE, 'mm') then trunc(t.BUSINESSDATE, 'd') - 6 else trunc(t.BUSINESSDATE, 'mm') end begindate from f_vsmsysdate t) and d.BUSINESSDATE group by a1.MANAGERID, b1.proid) x, (select a.proid, a.managerid, count(1) signcount FROM F_accinvestbill a,f_vsmsysdate d where a.ACTUALPAYDATE between (select case when trunc(t.BUSINESSDATE, 'd') - 6 > trunc(t.BUSINESSDATE, 'mm') then trunc(t.BUSINESSDATE, 'd') - 6 else trunc(t.BUSINESSDATE, 'mm') end begindate from f_vsmsysdate t) and d.BUSINESSDATE group by a.proid, a.managerid) y where u.id = r.userid and u.id = f.userid and f.proid = p.ID and r.roleid = s.id and s.rolecode = 'FX02' and v.id = u.orgid and u.validflag in ('0', '1') and r.useflag = '1' and p.PROTYPE in ('00', '01', '06') and x.MANAGERID(+) = u.id and x.proid(+) = f.proid and y.proid(+) = f.proid and u.id = y.managerid(+)
然而却报了如下错;
也就是说两个标量子查询对外关联了同一张表，SQL语句无法这么写，故而报错。
C:\Users>oerr ora 1417
01417, 00000, "a table may be outer joined to at most one other table"
// *Cause: a.b (+) = b.b and a.c (+) = c.c is not allowed
// *Action: Check that this is really what you want, then join b and c first
// in a view.
然后无奈重新改写如下：
select v.usercode, v.username, v.orgid, v.suborgid, v.orgcode, v.id, v.PROTYPE, v.PROCODE, v.PRONAME, nvl(sum(case when b1.AUDITDATE <= d.BUSINESSDATE and b1.AUDITDATE >= (select case when trunc(t.BUSINESSDATE, 'd') - 6 > trunc(t.BUSINESSDATE, 'mm') then trunc(t.BUSINESSDATE, 'd') - 6 else trunc(t.BUSINESSDATE, 'mm') end begindate from f_vsmsysdate t) then b1.lendamt else 0 end),0) AchieveAmt, count(case when a.ACTUALPAYDATE <= d.BUSINESSDATE and a.ACTUALPAYDATE >= (select case when trunc(t.BUSINESSDATE, 'd') - 6 > trunc(t.BUSINESSDATE, 'mm') then trunc(t.BUSINESSDATE, 'd') - 6 else trunc(t.BUSINESSDATE, 'mm') end begindate from f_vsmsysdate t) then 1 else 0 end) signCount from (select u.usercode, u.username, u.orgid, u.suborgid, v.orgcode, u.id, p.PROTYPE, p.PROCODE, p.PRONAME, f.proid from f_vsmuser u, F_SMROLE s, F_SMUSERROLE r, f_vsmorg v, f_vsmsysdate d, f_smuserpro f, f_vsmproduct p where u.id = r.userid and u.id = f.userid and f.proid = p.ID and r.roleid = s.id and s.rolecode = 'FX02' and v.id = u.orgid and u.validflag in ('0', '1') and r.useflag = '1' and p.PROTYPE in ('00', '01', '06')) v, f_vsmsysdate d, f_investapplyinfo a1, f_accplancollectreceipt b1, F_accinvestbill a where b1.mainapplyid = a1.id and a1.MANAGERID(+) = v.id and b1.proid(+) = v.proid and a.proid(+) = v.proid and a.managerid(+) = v.id group by v.usercode, v.username, v.orgid, v.suborgid, v.orgcode, v.id, v.PROTYPE, v.PROCODE, v.PRONAME, v.proid;
但是查询出来的只有1w条数据，显然是不行的和原来的结果是不一样的。
然后继续修改，索性直接把标量子查询全盘拿下来，原来的查询结果作为一张临时视图来看，修改SQL如下：
select z.usercode, z.username, z.orgid, z.suborgid, z.orgcode, z.proid, z.id, z.PROTYPE, z.PROCODE, z.PRONAME, nvl(x.sum_leadamt, 0) AchieveAmt, y.signCount from (select u.usercode, u.username, u.orgid, u.suborgid, v.orgcode, f.proid, u.id, p.PROTYPE, p.PROCODE, p.PRONAME from f_vsmuser u, F_SMROLE s, F_SMUSERROLE r, f_vsmorg v, f_vsmsysdate d, f_smuserpro f, f_vsmproduct p where u.id = r.userid and u.id = f.userid and f.proid = p.ID and r.roleid = s.id and s.rolecode = 'FX02' and v.id = u.orgid and u.validflag in ('0', '1') and r.useflag = '1' and p.PROTYPE in ('00', '01', '06')) z, (select a1.MANAGERID, b1.proid, sum(b1.lendamt) sum_leadamt from f_investapplyinfo a1, f_accplancollectreceipt b1,f_vsmsysdate d where b1.mainapplyid = a1.id and b1.AUDITDATE between (select case when trunc(t.BUSINESSDATE, 'd') - 6 > trunc(t.BUSINESSDATE, 'mm') then trunc(t.BUSINESSDATE, 'd') - 6 else trunc(t.BUSINESSDATE, 'mm') end begindate from f_vsmsysdate t) and d.BUSINESSDATE group by a1.MANAGERID, b1.proid) x, (select a.proid, a.managerid, count(1) signcount FROM F_accinvestbill a,f_vsmsysdate d where a.ACTUALPAYDATE between (select case when trunc(t.BUSINESSDATE, 'd') - 6 > trunc(t.BUSINESSDATE, 'mm') then trunc(t.BUSINESSDATE, 'd') - 6 else trunc(t.BUSINESSDATE, 'mm') end begindate from f_vsmsysdate t) and d.BUSINESSDATE group by a.proid, a.managerid) y where x.MANAGERID(+) = z.id and x.proid(+) = z.proid and y.proid(+) = z.proid and z.id = y.managerid(+)
然后执行结果正好和原查询结果一致，对比数据也都一样，执行计划如下：
执行时间从143s变成了2.7s，逻辑读从1亿降到了2.4w，瞬间爆炸。
然后提交开发同事，之后等待上线观察效果。
over！