一次sql脚本的优化
说明
做了一次sql脚本的优化。结果还是比较满意的。执行时间从2分半钟降到了3秒左右。
从过程中看，其实得到这个结果并不是因为优化做得多么出色，而是因为以前的脚本写得太烂。换句话说，这一次的优化并不是把一个正常的脚本修改得很出色，而是把一个糟糕的脚本修改得比较正常。
不过我比较知足。一方面用户接受了这个结果。另一方面，那个糟糕的脚本本来也是我写的……
分析
原先的脚本之所以这么糟糕，主要原因是子查询太多。几乎每一个字段都有一个子查询语句。而且每个子查询都是先获取一个全集，然后从全集中去查所需要的数据。然而实际上，子查询只需要以一个子集为依据。
优化策略
所以这次优化的策略是：先把最终需要的字段分成三组，对应的把全集拆分成三个子集。先从子集里查出所需的每组字段，然后再对三组字段进行连接。从子集里查出各组字段也不再使用子查询的方式，而是尽可能的使用连接。
其实就只做了这么一点事情。简单地说就是去掉了不必要的重复操作，以及缩小查询的起始范围。另外，作为个人的一点收获，对sql的一些语法也更熟悉了些。
贴一小段代码吧。觉得前后一样烂的兄弟们请轻拍，IT何苦为难IT。
优化前
select u.userCode,
u.userName,
u.comCode,
(select o.comcname
from t_atip_organization o
where o.comcode = u.comcode) as comName,
ym.sjmc,
-- 主審項目數
(select count(distinct p.projectapprovalcode)
from t_atip_projectapproval p, t_atip_prostatustrack ps
where p.chiefaudit = u.usercode
and p.projectlevel = '1'
and p.projectyear = substr(ym.sjmc, 0, 4)
and ps.projectapprovalcode = p.projectapprovalcode
and ps.projectstutas > '04'
and ps.seqno =
(select max(seqno)
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode = ps.projectapprovalcode)
and ym.sjmc =
(select distinct to_char(max(updatedate), 'yyyy-MM')
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode =
substr(p.projectapprovalcode, 0, 21))) as chiefAuditCount,
-- 主審項目中發現問題總數
(select count(distinct(prob.seqno))
from t_atip_problem prob,
t_atip_projectapproval proj,
t_atip_prostatustrack ps
where prob.programcode = proj.projectapprovalcode
and proj.chiefaudit = u.usercode
and prob.isrecall = '1'
and proj.projectyear = substr(ym.sjmc, 0, 4)
and ps.projectapprovalcode = proj.projectapprovalcode
and ps.projectstutas > '04'
and ps.seqno =
(select max(seqno)
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode = ps.projectapprovalcode)
and ym.sjmc =
(select distinct to_char(max(updatedate), 'yyyy-MM')
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode =
substr(proj.projectapprovalcode, 0, 21))) as chiefProbelCount,
-- 主審項目中發現的重大問題總數
(select count(distinct(prob.seqno))
from t_atip_problem prob,
t_atip_projectapproval proj,
t_atip_prostatustrack ps
where prob.programcode = proj.projectapprovalcode
and prob.problemlevel in ('13', '14')
and proj.chiefaudit = u.usercode
and prob.isrecall = '1'
and proj.projectyear = substr(ym.sjmc, 0, 4)
and ps.projectapprovalcode = proj.projectapprovalcode
and ps.projectstutas > '04'
and ps.seqno =
(select max(seqno)
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode = ps.projectapprovalcode)
and ym.sjmc =
(select distinct to_char(max(updatedate), 'yyyy-MM')
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode =
substr(proj.projectapprovalcode, 0, 21))) as chiefMainProbCount,
-- 作為主審的被評分項目數
(select count(distinct p.projectapprovalcode)
from t_atip_projectevaluation eval,
t_atip_projectapproval p,
t_atip_prostatustrack ps
where (eval.projectcode = p.projectapprovalcode or
p.projectapprovalcode =
substr(eval.projectcode, 0, length(eval.projectcode) - 3))
and p.chiefaudit = u.usercode
and eval.usercode = u.usercode
and p.projectlevel = '1'
and p.projectyear = substr(ym.sjmc, 0, 4)
and ps.projectapprovalcode = p.projectapprovalcode
and ps.projectstutas > '04'
and ps.seqno =
(select max(seqno)
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode = ps.projectapprovalcode)
and ym.sjmc =
(select distinct to_char(max(updatedate), 'yyyy-MM')
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode =
substr(p.projectapprovalcode, 0, 21))) as chiefGradedProjectCount,
--作為主審被評分的總分
/*(select round(nvl(avg(sum(eval.score)), 0), 2) as avgscore
from t_atip_projectevaluation eval,
t_atip_projectapproval p,
t_atip_prostatustrack ps
where (eval.projectcode = p.projectapprovalcode or
p.projectapprovalcode =
substr(eval.projectcode, 0, length(eval.projectcode) - 3))
and p.chiefaudit = u.usercode
and eval.usercode = u.usercode
and p.projectlevel = '1'
and p.projectyear = substr(ym.sjmc, 0, 4)
and ps.projectapprovalcode = p.projectapprovalcode
and ps.projectstutas > '04'
and ps.seqno =
(select max(seqno)
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode = ps.projectapprovalcode)
and ym.sjmc =
(select distinct to_char(max(updatedate), 'yyyy-MM')
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode =
substr(p.projectapprovalcode, 0, 21))
group by eval.projectcode, eval.evaluationusercode)*/
(SELECT SUM(ma.avgtotalscore)
FROM (SELECT m.projectcode,
m.usercode,
AVG(projtotalscore) avgtotalscore
FROM (select substr(eval.projectcode, 0, 21) projectcode,
eval.usercode,
eval.evaluationusercode,
sum(nvl(eval.score,0)) as projtotalscore
from t_atip_projectevaluation eval
group by substr(eval.projectcode, 0, 21),
eval.evaluationusercode,
eval.usercode) m
GROUP BY m.projectcode, m.usercode) ma,
t_atip_projectapproval p,
t_atip_prostatustrack ps
WHERE ma.projectcode = p.projectapprovalcode
AND p.projectlevel = '1'
AND p.projectyear = substr(ym.sjmc, 0, 4)
AND ma.usercode = p.chiefaudit
AND p.chiefaudit = u.usercode
and ps.projectapprovalcode = p.projectapprovalcode
and ps.projectstutas > '04'
and ps.seqno =
(select max(seqno)
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode = ps.projectapprovalcode)
and ym.sjmc =
(select distinct to_char(max(updatedate), 'yyyy-MM')
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode =
substr(p.projectapprovalcode, 0, 21))
GROUP BY ma.usercode)
as chiefTotalGrade,
-- 作為主審被質檢打分的項目數
(select count(distinct qual.projectcode)
from t_atip_qualitytype qual,
t_atip_projectapproval proj,
t_atip_prostatustrack ps
where qual.projectcode = proj.projectapprovalcode
and proj.chiefaudit = u.usercode
and proj.projectlevel = '1'
and proj.projectyear = substr(ym.sjmc, 0, 4)
and ps.projectapprovalcode = proj.projectapprovalcode
and ps.projectstutas > '04'
and ps.seqno =
(select max(seqno)
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode = ps.projectapprovalcode)
and ym.sjmc =
(select distinct to_char(max(updatedate), 'yyyy-MM')
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode =
substr(proj.projectapprovalcode, 0, 21))) as chiefQualifiedProjectCount,
--作為主審被質檢打分的總分
(select nvl(sum(qual.score), 0)
from t_atip_qualitytype qual,
t_atip_projectapproval proj,
t_atip_prostatustrack ps
where qual.projectcode = proj.projectapprovalcode
and proj.chiefaudit = u.usercode
and proj.projectlevel = '1'
and proj.projectyear = substr(ym.sjmc, 0, 4)
and ps.projectapprovalcode = proj.projectapprovalcode
and ps.projectstutas > '04'
and ps.seqno =
(select max(seqno)
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode = ps.projectapprovalcode)
and ym.sjmc =
(select distinct to_char(max(updatedate), 'yyyy-MM')
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode =
substr(proj.projectapprovalcode, 0, 21))) as chiefTotalQualified
from t_atip_user u,
(select to_char(add_months(date '2003-12-01', 1 * rownum), 'yyyy-MM') sjmc
from dual
where 1 = 1
connect by rownum <
(select months_between(trunc(sysdate, 'mm'),
trunc(date '2003-12-01', 'mm'))
from dual) + 1) ym
where u.validstatus = '1'
and u.istemporary = '0'
优化后
select USERCODE,
COMCODE,
SJMC,
sum(CHIEFAUDITCOUNT) as CHIEFAUDITCOUNT,
sum(CHIEFEVECTIONCOUNT) as CHIEFEVECTIONCOUNT,
sum(CHIEFTOTALQUALIFIED) as CHIEFTOTALQUALIFIED,
sum(CHIEFQUALIFIEDPROJECTCOUNT) as CHIEFQUALIFIEDPROJECTCOUNT,
sum(CHIEFTOTALGRADE) as CHIEFTOTALGRADE,
sum(CHIEFGRADEDPROJECTCOUNT) as CHIEFGRADEDPROJECTCOUNT,
sum(CHIEFMAINPROBCOUNT) as CHIEFMAINPROBCOUNT,
sum(CHIEFPROBELCOUNT) as CHIEFPROBELCOUNT
from (
-- 只計算項目數和出差天數，其它全部置為0
select u.userCode,
u.comCode,
(to_char(ps.updatedate, 'yyyy-MM')) as sjmc,
-- 主審項目數
count(proj.projectapprovalcode) as chiefAuditCount,
-- 主審出差天數
sum(proj.totalday) as chiefEvectionCount,
-- 作為主審被質檢打分的總分，暫時繞過
0 as chiefTotalQualified,
-- 作為主審被質檢打分的項目數
0 as chiefQualifiedProjectCount,
-- 主審被評分項目總分，暫時繞過吧……
0 as chiefTotalGrade,
-- 主審被評分項目數
0 as chiefGradedProjectCount,
-- 主審項目中發現重大問題總數
0 as chiefMainProbCount,
--主審項目中發現問題總數
0 as chiefProbelCount
from t_atip_user u,
t_atip_prostatustrack ps,
t_atip_projectapproval proj
where ps.projectapprovalcode = proj.projectapprovalcode
and ps.projectstutas > '04'
and ps.seqno =
(select max(seqno)
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode = ps.projectapprovalcode)
and proj.projectyear = to_char(ps.updatedate, 'yyyy')
and proj.chiefaudit = u.usercode
and proj.projectlevel = '1'
and u.validstatus = '1'
and u.istemporary = '0'
group by u.usercode, u.comcode, to_char(ps.updatedate, 'yyyy-MM')
union all
-- 只計算主審項目中發現重大問題總數和發現問題總數，其它全部置為0
select u.userCode,
u.comCode,
(to_char(ps.updatedate, 'yyyy-MM')) as sjmc,
-- 主審項目數
0 as chiefAuditCount,
-- 主審出差天數
0 as chiefEvectionCount,
-- 作為主審被質檢打分的總分，暫時繞過
0 as chiefTotalQualified,
-- 作為主審被質檢打分的項目數
0 as chiefQualifiedProjectCount,
-- 主審被評分項目總分，暫時繞過吧……
0 as chiefTotalGrade,
-- 主審被評分項目數
0 as chiefGradedProjectCount,
-- 主審項目中發現重大問題總數
count(distinct mainprob.seqno) as chiefMainProbCount,
--主審項目中發現問題總數
count(distinct prob.seqno) as chiefProbelCount
from t_atip_user u,
t_atip_prostatustrack ps,
t_atip_projectapproval proj
left join t_atip_problem prob on prob.programcode =
proj.projectapprovalcode
and prob.isrecall = '1'
left join t_atip_problem mainprob on mainprob.seqno = prob.seqno
and mainprob.problemlevel in
('13', '14')
where ps.projectapprovalcode = proj.projectapprovalcode
and ps.projectstutas > '04'
and ps.seqno =
(select max(seqno)
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode = ps.projectapprovalcode)
and proj.projectyear = to_char(ps.updatedate, 'yyyy')
and proj.chiefaudit = u.usercode
and proj.projectlevel = '1'
and u.validstatus = '1'
and u.istemporary = '0'
group by u.usercode, u.comcode, to_char(ps.updatedate, 'yyyy-MM')
union all
-- 只計算主審被評分項目總分和主審被評分項目數，其它全部置為0
-- 主審被評分總分的計算過程比較複雜，這裡採用的仍是優化之前的語句
select USERCODE,
COMCODE,
SJMC,
CHIEFAUDITCOUNT,
CHIEFEVECTIONCOUNT,
CHIEFTOTALQUALIFIED,
CHIEFQUALIFIEDPROJECTCOUNT,
round(nvl(sum(CHIEFTOTALGRADE), 0), 2) as CHIEFTOTALGRADE,
nvl(sum(CHIEFGRADEDPROJECTCOUNT), 0) as CHIEFGRADEDPROJECTCOUNT,
CHIEFMAINPROBCOUNT,
CHIEFPROBELCOUNT
from (select u.userCode,
u.userName,
u.comCode,
o.comcname as comName,
(to_char(ps.updatedate, 'yyyy-MM')) as sjmc,
-- 主審出差天數
0 as chiefEvectionCount,
-- 作為主審被質檢打分的總分
0 as chiefTotalQualified,
-- 作為主審被質檢打分的項目數
0 as chiefQualifiedProjectCount,
-- 主審被評分項目總分
(SELECT SUM(ma.avgtotalscore)
FROM (SELECT m.projectcode,
m.usercode,
AVG(projtotalscore) avgtotalscore
FROM (select substr(eval.projectcode, 0, 21) projectcode,
eval.usercode,
eval.evaluationusercode,
sum(nvl(eval.score, 0)) as projtotalscore
from t_atip_projectevaluation eval
group by substr(eval.projectcode, 0, 21),
eval.evaluationusercode,
eval.usercode) m
GROUP BY m.projectcode, m.usercode) ma
WHERE ma.projectcode = proj.projectapprovalcode
AND proj.projectlevel = '1'
AND proj.projectyear =
to_char(ps.updatedate, 'yyyy')
AND ma.usercode = proj.chiefaudit
AND proj.chiefaudit = u.usercode
and ps.projectapprovalcode =
proj.projectapprovalcode
and ps.projectstutas > '04'
and ps.seqno =
(select max(seqno)
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode =
ps.projectapprovalcode)
GROUP BY ma.usercode) as chiefTotalGrade,
-- 主審被評分項目數
(select sum(count(distinct eval.projectcode))
from t_atip_projectevaluation eval
where eval.projectcode = proj.projectapprovalcode
and eval.usercode = proj.chiefaudit
group by eval.projectcode) as chiefGradedProjectCount,
-- 主審項目中發現重大問題總數
0 as chiefMainProbCount,
--主審項目中發現問題總數
0 as chiefProbelCount,
-- 主審項目數
0 as chiefAuditCount
from t_atip_user u,
t_atip_prostatustrack ps,
t_atip_organization o,
t_atip_projectapproval proj
where o.comcode = u.comcode
and ps.projectapprovalcode = proj.projectapprovalcode
and ps.projectstutas > '04'
and ps.seqno = (select max(seqno)
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode =
ps.projectapprovalcode)
and proj.projectyear = to_char(ps.updatedate, 'yyyy')
and proj.chiefaudit = u.usercode
and proj.projectlevel = '1')
group by USERCODE, COMCODE, SJMC
union all
-- 只計算質檢項目總數和質檢總分，其它全部置為0
select u.userCode,
u.comCode,
(to_char(ps.updatedate, 'yyyy-MM')) as sjmc,
-- 主審項目數
0 as chiefAuditCount,
-- 主審出差天數
0 as chiefEvectionCount,
-- 作為主審被質檢打分的總分
round(nvl(sum(qual.score), 0), 2) as chiefTotalQualified,
-- 作為主審被質檢打分的項目數
count(distinct qual.projectcode) as chiefQualifiedProjectCount,
-- 主審被評分項目總分
0 as chiefTotalGrade,
-- 主審被評分項目數
0 as chiefGradedProjectCount,
-- 主審項目中發現重大問題總數
0 as chiefMainProbCount,
--主審項目中發現問題總數
0 as chiefProbelCount
from t_atip_user u,
t_atip_prostatustrack ps,
t_atip_projectapproval proj
left join t_atip_qualitytype qual on qual.projectcode =
proj.projectapprovalcode
where ps.projectapprovalcode = proj.projectapprovalcode
and ps.projectstutas > '04'
and ps.seqno =
(select max(seqno)
from t_atip_prostatustrack tempps
where tempps.projectapprovalcode = ps.projectapprovalcode)
and proj.projectyear = to_char(ps.updatedate, 'yyyy')
and proj.chiefaudit = u.usercode
and proj.projectlevel = '1'
and u.validstatus = '1'
and u.istemporary = '0'
group by u.usercode, u.comcode, to_char(ps.updatedate, 'yyyy-MM'))
group by USERCODE, COMCODE, SJMC