oracle 中 sql语句优化问题，求教大佬！！！

[Aex_]

下面这个视图查询需要 20多秒，大佬们知道怎么优化下吗，经常导致页面卡死！！！


create or replace view v_st_receiverdataview as
select "RECEIVEPARTID","RECEIVEPARTNAME","RECEIVEDCOUNT","RECEIVEDONTIME","REGISTEREDCOUNT","REGISTEREDCOUNTONTIME",registerederrorcount,"VERIFIEDMSGCOUNTSENT","COMPLETEDCOUNT","CHECKMSGCOUNTSENT","CHECKMSGCOUNT","CREATETIME","RECID","TASKNUM","MAINTYPENAME","SUBTYPENAME","RECDESC","ADDRESS","DISTRICTCODE","DISTRICTNAME","CHECKMSGCOUNTSENTERROR","SIMILARREGISTEREDCOUNT","RECLEVELNAME","RECLEVELID",recflag from (
select ta.partid as receivepartid,
ta.partname as receivepartname,
1 as receivedcount,
1 as receivedontime,
decode(t.actpropertyid,102,0,1) registeredcount,
(case
when ta.endtime is not null and t.actpropertyid<> 102
and (ta.endtime - ta.createtime)*60*24*60<600
then 1
else 0 end
)
as registeredcountontime,
0 as registerederrorcount,
0 as verifiedmsgcountsent,
0 as completedcount,
0 as checkmsgcountsent,
0 as checkmsgcount,
t.createtime,
t.recid,
t.tasknum,
t.maintypename,
t.subtypename,
t.recdesc,
t.address,
t.districtcode,
t.districtname,
t.checkmsgcountsenterror,
t.similarregisteredcount,t.reclevelname,t.reclevelid,
'a' recflag
from scstat.t_qs_rec t,(select t1.* from scdata.t_wf_actinst t1,
(select max(t.createtime) createtime ,t.recid from scdata.t_wf_actinst t where t.actdefid = 61 group by t.recid )
t2 where t1.createtime = t2.createtime and t1.recid = t2.recid and t1.actdefid = 61
) ta
where t.recid = ta.recid and ta.actdefid = 61
union all
select ta.partid as receivepartid,
ta.partname as receivepartname,
1 as receivedcount,
1 as receivedontime,
decode(t.actpropertyid,102,0,1) registeredcount,

(case
-- 市级案件 如果市区二级时间都不为空
when t.reclevelid =1 and t4.endtime is not null and t.actpropertyid<> 102
and ta.endtime is not null and (ta.endtime - t4.endtime)*60*24*60 < 600
then 1
when t.reclevelid =2 and ta.endtime is not null and t.actpropertyid<> 102
and (ta.endtime - ta.createtime)*60*24*60<600
then 1
--如果产生rollback的情况 reclevelid =1为市级 reclevelid =2为区级
when t.reclevelid =1 and t5.endtime is not null and t.actpropertyid<> 102
and t5.transworkitemname = 'rollback' and (t5.endtime - t5.createtime)*60*24*60 < 600
then 1
when t.reclevelid =2 and t3.endtime is not null and t.actpropertyid<> 102
and t3.transworkitemname = 'rollback' and (t3.endtime - t3.createtime)*60*24*60 < 600
then 1
else 0 end
)
as registeredcountontime,
case when t3.actid is not null and t.actpropertyid<> 102 and t5.actid is null
then 1
else 0 end as registerederrorcount,
0 as verifiedmsgcountsent,
0 as completedcount,
0 as checkmsgcountsent,
0 as checkmsgcount,
t.createtime,
t.recid,
t.tasknum,
t.maintypename,
t.subtypename,
t.recdesc,
t.address,
t.districtcode,
t.districtname,
t.checkmsgcountsenterror,
t.similarregisteredcount,t.reclevelname,t.reclevelid,
'b' recflag
from scstat.t_qs_rec t
left join
(select t1.* from scdata.t_wf_actinst t1,
(select max(t.createtime) createtime ,t.recid from scdata.t_wf_actinst t where t.actdefid = 70 group by t.recid )
t2 where t1.createtime = t2.createtime and t1.recid = t2.recid and t1.actdefid = 70
) ta
on t.recid = ta.recid and ta.actdefid = 70
left join
(select t1.* from scdata.t_wf_actinst t1,
(select max(t.createtime) createtime ,t.recid from scdata.t_wf_actinst t where t.actdefid = 70 and t.transworkitemname = 'rollback' group by t.recid )
t2 where t1.createtime = t2.createtime and t1.recid = t2.recid and t1.actdefid = 70 and t1.transworkitemname = 'rollback'
) t3
on t.recid = t3.recid and t3.actdefid = 70 and t3.transworkitemname = 'rollback'

left join
(select t1.* from scdata.t_wf_actinst t1,
(select max(t.createtime) createtime ,t.recid from scdata.t_wf_actinst t where t.actdefid = 61 group by t.recid )
t2 where t1.createtime = t2.createtime and t1.recid = t2.recid and t1.actdefid = 61
) t4
on t.recid = t4.recid and t4.actdefid = 61
left join
( select t1.* from scdata.t_wf_actinst t1,
(select max(t.createtime) createtime ,t.recid from scdata.t_wf_actinst t where t.actdefid = 61 and t.transworkitemname = 'rollback' group by t.recid )
t2 where t1.createtime = t2.createtime and t1.recid = t2.recid and t1.actdefid = 61 and t1.transworkitemname = 'rollback'
) t5 on t.recid = t5.recid and t5.actdefid = 61
where ta.partname not like '%zx%' and ta.partname not like '%XZJ%'
) t where
t.receivepartname not like '%值班长%' and
t.receivepartname not like '%bz%'

and t.receivedcount > 0
;

[Aex_]

create or replace view v_st_receiverdataview as select "RECEIVEPARTID","RECEIVEPARTNAME","RECEIVEDCOUNT","RECEIVEDONTIME","REGISTEREDCOUNT","REGISTEREDCOUNTONTIME",registerederrorcount,"VERIFIEDMSGCOUNTSENT","COMPLETEDCOUNT","CHECKMSGCOUNTSENT","CHECKMSGCOUNT","CREATETIME","RECID","TASKNUM","MAINTYPENAME","SUBTYPENAME","RECDESC","ADDRESS","DISTRICTCODE","DISTRICTNAME","CHECKMSGCOUNTSENTERROR","SIMILARREGISTEREDCOUNT","RECLEVELNAME","RECLEVELID",recflag from ( select ta.partid as receivepartid, ta.partname as receivepartname, 1 as receivedcount, 1 as receivedontime, decode(t.actpropertyid,102,0,1) registeredcount, (case when ta.endtime is not null and t.actpropertyid<> 102 and (ta.endtime - ta.createtime)*60*24*60<600 then 1 else 0 end ) as registeredcountontime, 0 as registerederrorcount, 0 as verifiedmsgcountsent, 0 as completedcount, 0 as checkmsgcountsent, 0 as checkmsgcount, t.createtime, t.recid, t.tasknum, t.maintypename, t.subtypename, t.recdesc, t.address, t.districtcode, t.districtname, t.checkmsgcountsenterror, t.similarregisteredcount,t.reclevelname,t.reclevelid, 'a' recflag from scstat.t_qs_rec t,(select t1.* from scdata.t_wf_actinst t1, (select max(t.createtime) createtime ,t.recid from scdata.t_wf_actinst t where t.actdefid = 61 group by t.recid ) t2 where t1.createtime = t2.createtime and t1.recid = t2.recid and t1.actdefid = 61 ) ta where t.recid = ta.recid and ta.actdefid = 61 union all select ta.partid as receivepartid, ta.partname as receivepartname, 1 as receivedcount, 1 as receivedontime, decode(t.actpropertyid,102,0,1) registeredcount, (case -- 市级案件 如果市区二级时间都不为空 when t.reclevelid =1 and t4.endtime is not null and t.actpropertyid<> 102 and ta.endtime is not null and (ta.endtime - t4.endtime)*60*24*60 < 600 then 1 when t.reclevelid =2 and ta.endtime is not null and t.actpropertyid<> 102 and (ta.endtime - ta.createtime)*60*24*60<600 then 1 --如果产生rollback的情况 reclevelid =1为市级 reclevelid =2为区级 when t.reclevelid =1 and t5.endtime is not null and t.actpropertyid<> 102 and t5.transworkitemname = 'rollback' and (t5.endtime - t5.createtime)*60*24*60 < 600 then 1 when t.reclevelid =2 and t3.endtime is not null and t.actpropertyid<> 102 and t3.transworkitemname = 'rollback' and (t3.endtime - t3.createtime)*60*24*60 < 600 then 1 else 0 end ) as registeredcountontime, case when t3.actid is not null and t.actpropertyid<> 102 and t5.actid is null then 1 else 0 end as registerederrorcount, 0 as verifiedmsgcountsent, 0 as completedcount, 0 as checkmsgcountsent, 0 as checkmsgcount, t.createtime, t.recid, t.tasknum, t.maintypename, t.subtypename, t.recdesc, t.address, t.districtcode, t.districtname, t.checkmsgcountsenterror, t.similarregisteredcount,t.reclevelname,t.reclevelid, 'b' recflag from scstat.t_qs_rec t left join (select t1.* from scdata.t_wf_actinst t1, (select max(t.createtime) createtime ,t.recid from scdata.t_wf_actinst t where t.actdefid = 70 group by t.recid ) t2 where t1.createtime = t2.createtime and t1.recid = t2.recid and t1.actdefid = 70 ) ta on t.recid = ta.recid and ta.actdefid = 70 left join (select t1.* from scdata.t_wf_actinst t1, (select max(t.createtime) createtime ,t.recid from scdata.t_wf_actinst t where t.actdefid = 70 and t.transworkitemname = 'rollback' group by t.recid ) t2 where t1.createtime = t2.createtime and t1.recid = t2.recid and t1.actdefid = 70 and t1.transworkitemname = 'rollback' ) t3 on t.recid = t3.recid and t3.actdefid = 70 and t3.transworkitemname = 'rollback' left join (select t1.* from scdata.t_wf_actinst t1, (select max(t.createtime) createtime ,t.recid from scdata.t_wf_actinst t where t.actdefid = 61 group by t.recid ) t2 where t1.createtime = t2.createtime and t1.recid = t2.recid and t1.actdefid = 61 ) t4 on t.recid = t4.recid and t4.actdefid = 61 left join ( select t1.* from scdata.t_wf_actinst t1, (select max(t.createtime) createtime ,t.recid from scdata.t_wf_actinst t where t.actdefid = 61 and t.transworkitemname = 'rollback' group by t.recid ) t2 where t1.createtime = t2.createtime and t1.recid = t2.recid and t1.actdefid = 61 and t1.transworkitemname = 'rollback' ) t5 on t.recid = t5.recid and t5.actdefid = 61 where ta.partname not like '%zx%' and ta.partname not like '%XZJ%' ) t where t.receivepartname not like '%值班长%' and t.receivepartname not like '%bz%' and t.receivedcount > 0 ;

[maradona1984]

别人不知道你的数据结构,不知道表之间的关系,不知道每个表的数据量,索引情况,数据库版本,机器配置,怎么给你优化?除非神人 简单的还好说,你这貌似跟简单不搭边 我建议你去看下执行计划,慢慢优化吧