求高手帮忙优化语句

nfy513 2012-11-14 05:08:13
求高手帮忙优化
with hisdata
as (select sum(1) over(partition by usersub.mobile,sub.substation_id,alarm.type,alarm.level,ai.pid
order by usersub.mobile,sub.substation_id,alarm.type,alarm.level,ai.pid,cdatetime ) xh
,sub.pname
,sub.substation_id
,ai.pid
,ai.item_code
,itm.item_name item_name
,his.cdatetime
,(case when sub.sub_type=5 then his.cvalue else his.cvtval end) val
,std.high
,usersub.mobile
,alarm.type
,(case when sub.sub_type=5 then (case when info.admin_code in (1,2) then alarm.alarm_value else 2*alarm.alarm_value end)
when sub.sub_type=6 then (case when info.admin_code in (1,2)
then (case alarm.alarm_value when 6 then 2 when 10 then 4 when 14 then 6 when 24 then 8 end)
else (case alarm.alarm_value when 6 then 4 when 10 then 8 when 14 then 12 when 24 then 16 end) end)
end) alarm_value
,max(cdatetime) over(partition by ai.pid) maxdate
from hisdata_1h his
inner join tai ai
on his.pid=ai.pid
and ai.item_code in (201,311,316)
and his.cdatetime>=trunc(sysdate,'hh')-2.5 and his.cdatetime<=trunc(sysdate,'hh') - 1/24
inner join item itm
on ai.item_code=itm.item_code
inner join substation sub
on ai.substation_id=sub.substation_id
and sub.sub_type in (5,6)
inner join ai ai0
on ai0.substation_id=sub.substation_id
and ai0.item_code in (210,492)
inner join hisdata_1h his0
on ai0.pid=his0.pid
and his0.cdatetime=his.cdatetime
and his0.cdatetime>=trunc(sysdate,'hh')-2.5 and his0.cdatetime<=trunc(sysdate,'hh') - 1/24
and his0.qcode=0 and nvl(his0.cvalue,0)>10
inner join pollution_info info
on sub.pcode_c=info.enterprise_code
and sub.pcode=info.pwk_code
and sub.stcode=info.stcode
and sub.cyear=info.cyear
inner join v_wp_standard std
on ai.item_code=std.item_code
and ai.scope_id=std.scope_id
and ai.poll_standard = std.standard
inner join v_user_substation usersub
on sub.substation_id=usersub.substation_id and usersub.mobile is not null
inner join alarm_define alarm
on type in (11,13)
and usersub.level>=alarm.level
left join station_status st
on sub.substation_id=st.substation_id
and not (st.endtime<his.cdatetime or st.starttime>=his.cdatetime+1/24)
and ((st.type_code in (25,27) and (st.code in (5,6) or st.code=ai.item_code)) or st.type_code=23)
and st.check=1
and his.cdatetime<=st.endtime and his.cdatetime>=st.starttime
where st.substation_id is null
)
select mobile
,pname
,type
,item_name
,alarm_value
,to_char(maxdate,'mm')||'月'||to_char(maxdate,'dd')||'日'||to_char(maxdate,'hh24')||'时' dt
,sum(case when cdatetime>=trunc(sysdate,'hh')-alarm_value/24 then 1 else 0 end) sms
from hisdata
where cdatetime>=trunc(sysdate,'hh')-(2+alarm_value)/24
and type=11
group by mobile,substation_id,pname,item_name,maxdate,type,larm_value
having sum(case when cdatetime>=trunc(sysdate,'hh')-alarm_value/24 and val<=high then 1 else 0 end) = 0
and sum(case when cdatetime=trunc(sysdate,'hh')-alarm_value/24 and val>high then 1 else 0 end) = 1
and sum(case when cdatetime=trunc(sysdate,'hh')-(1+alarm_value)/24 and val>high then 1 else 0 end)=0
and sum(case when cdatetime>=trunc(sysdate,'hh')-alarm_value/24 then 1 else 0 end)>alarm_value*0.8
union all
select h1.mobile
,h1.pname
,h1.type
,h1.item_name
,h1.alarm_value
,to_char(h1.maxdate,'mm')||'月'||to_char(h1.maxdate,'dd')||'日'||to_char(h1.maxdate,'hh24')||'时' dt
,sum(case when h1.cdatetime>=trunc(sysdate,'hh')+(1-h1.alarm_value)/24 and h1.val=h2.val then 1 else 0 end)+1 sms
from hisdata h1
inner join hisdata h2
on h1.mobile=h2.mobile
and h1.pid=h2.pid
and h1.xh = h2.xh+1
and h1.alarm_value = h2.alarm_value
where h1.cdatetime>=trunc(sysdate,'hh')-(h1.alarm_value)/24
and h1.type=13
and h2.type=13
group by h1.mobile,h1.substation_id,h1.pname,h1.item_name,h1.maxdate,h1.type,h1.alarm_value
having sum(case when h1.cdatetime>=trunc(sysdate,'hh')+(1-h1.alarm_value)/24 and h1.val<>h2.val then 1 else 0 end)=0
and sum(case when h1.cdatetime=trunc(sysdate,'hh')+(1-h1.alarm_value)/24 and h1.val=h2.val then 1 else 0 end)=1
and sum(case when h1.cdatetime=trunc(sysdate,'hh')+(-h1.alarm_value)/24 and h1.val=h2.val then 1 else 0 end)=0
and sum(case when h1.cdatetime>=trunc(sysdate,'hh')+(1-h1.alarm_value)/24 and h1.val=h2.val then 1 else 0 end)+1>h1.c0109_alarm_value*0.8
...全文
133 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
  • 打赏
  • 举报
回复
各种 inner join
nfy513 2012-11-15
  • 打赏
  • 举报
回复
什么意思?没啥执行计划,就是程序里调用这个存储过程,执行出数据很慢,接近10分钟
linwaterbin 2012-11-14
  • 打赏
  • 举报
回复
执行计划和统计信息贴上来

3,492

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧