select null,s1.x_partner_bureau,count(1) r from (
select req3.sr_num,req3.created,req3.sr_stat_id, req2.x_owner_dept,req4.x_partner_bureau
from temp_v_SR_HIS req2,temp_v_srv_req req3,temp_v_org_ext req4,
(
SELECT * FROM
(SELECT a.row_id, ROW_NUMBER() OVER(PARTITION BY a.par_row_id ORDER BY a.created ) RN
FROM (select s2.row_id,s2.par_row_id,s2.created
from temp_v_SRV_REQ s1,temp_v_SR_HIS s2
where s2.X_ACT_STR_TIME >=trunc(sysdate) and s2.X_ACT_STR_TIME<=trunc(sysdate+1)-1/24/60/60 //注释
and s1.x_sr_category='投诉'
and s1.row_id=s2.par_row_id
and s2.x_old_value='待处理'
group by s2.par_row_id,s2.row_id,s2.created
) a
) WHERE RN = 1) req1
where req2.row_id=req1.row_id
and req3.row_id=req2.par_row_id
and req2.x_owner_dept = req4.name
and req4.int_org_flg='Y') s1 group by s1.x_partner_bureau order by count(1) desc
这个SQL查询要100秒左右,
上面注释的这一行如果把s2.X_ACT_STR_TIME这个时间字段换成s1.ACT_OPEN_DT(也是时间字段),查询是秒查询
而S1和S2里面的数据量差不多,
求大神解决如何优化SQL
select req3.sr_num,req3.created,req3.sr_stat_id, req2.x_owner_dept,req4.x_partner_bureau
from temp_v_SR_HIS req2,temp_v_srv_req req3,temp_v_org_ext req4,
(
SELECT * FROM
(SELECT a.row_id, ROW_NUMBER() OVER(PARTITION BY a.par_row_id ORDER BY a.created ) RN
FROM (select s2.row_id,s2.par_row_id,s2.created
from temp_v_SRV_REQ s1,temp_v_SR_HIS s2
where s2.X_ACT_STR_TIME >=trunc(sysdate) and s2.X_ACT_STR_TIME<=trunc(sysdate+1)-1/24/60/60 //注释
and s1.x_sr_category='投诉'
and s1.row_id=s2.par_row_id
and s2.x_old_value='待处理'
group by s2.par_row_id,s2.row_id,s2.created
) a
) WHERE RN = 1) req1
where req2.row_id=req1.row_id
and req3.row_id=req2.par_row_id
and req2.x_owner_dept = req4.name
and req4.int_org_flg='Y') s1 group by s1.x_partner_bureau order by count(1) desc
这个SQL查询要100秒左右,
上面注释的这一行如果把s2.X_ACT_STR_TIME这个时间字段换成s1.ACT_OPEN_DT(也是时间字段),查询是秒查询
而S1和S2里面的数据量差不多,
求大神解决如何优化SQL
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货