各位好:
麻烦各位高人帮忙看看下面这两段脚本如何优化好,我修改后,15分钟还是跑不出数据,麻烦各位高手帮忙在不改变提数结果和字段的情况进行比较好的优化,由于这个比较急,所以在此向各位跪谢啦。
脚本如下:select distinct to_char(region) region, com, vc_subno, vc_dissrv,
vc_startdate, vc_enddate, vc_addinf, int_yxplanid, vc_rectype,
vc_dealdate, vc_logid, vc_applaydate
from indata_log
where (region, com, vc_subno, int_yxplanid, vc_dissrv, nvl(vc_addinf, '-'), vc_startdate,
case when vc_enddate is null or vc_enddate > '20200101000000' then '20300101000000'
else to_char(to_date(vc_enddate, 'yyyymmddhh24miss')-1/24/60/60, 'yyyymmddhh24miss') end)
in (
select region, 'WX' com, vc_subno, int_yxplanid, vc_dissrv,trim(nvl(vc_addinf, '-')), vc_startdate, /*af_trim*/
case when vc_enddate is null or vc_enddate > '20200101000000' then '20300101000000'
else to_char(to_date(vc_enddate, 'yyyymmddhh24miss')-1/24/60/60, 'yyyymmddhh24miss') end
from indata_log
where com = 'WX'
minus
select region, 'WX' com, vc_subno, int_yxplanid, vc_dissrv, vc_addinf, vc_startdate,
case when vc_enddate > '20200101000000' then '20300101000000' else vc_enddate end
from indata_log
where com = 'CA' and vc_source = '1' )
order by region, vc_applaydate);
------以上sql语句中有distinct的地方,建议修改成group by形式,避免产生不必要的排序。以上脚本如何在不改变提数结果及不改变字段和值的前提进行优化呢。
select to_char(a.applyoid) as recoid, '0' as subsid, a.privsetid as privid, c.disccode, d.billingnbr as affixinfo,
'BILLNBR' AS affixtype, a.startdate, a.enddate, to_char(a.grpsubsid) grpsubsid, 'D' as idu,e.recdate, '' as applyoid,
to_char(a.canceloid) as canceloid, b.memservnumber as servnumber, '0' as custid
from dhw_yxfn a, dhw_jtcy b, yxfn_djjh c, jtw_jtyh d,clrz e
where e.oid in (select oid from clrz where groupid in
(select groupid from clrz where oid in
(select recoid from group_member_recdetail where idu ='D' and recdate between :STARTDATE and :ENDDATE and memtype in (1,7))
and recdate between :STARTDATE and :ENDDATE )
and recdefid ='ChangeProduct')
and a.grpsubsmemoid = b.oid and a.region =%s/*and e.recdate between :STARTDATE and :ENDDATE*/
and a.privsetid = c.yxplanid and b.groupoid = d.subsid and a.canceloid=e.oid,esRegion.GetData());
--------以上语句如何在不改变提数结果及不改变字段和值的前提进行优化呢,偶优化了后,20分钟还是提不出数据,请高人指点。
麻烦各位高人帮忙看看下面这两段脚本如何优化好,我修改后,15分钟还是跑不出数据,麻烦各位高手帮忙在不改变提数结果和字段的情况进行比较好的优化,由于这个比较急,所以在此向各位跪谢啦。
脚本如下:select distinct to_char(region) region, com, vc_subno, vc_dissrv,
vc_startdate, vc_enddate, vc_addinf, int_yxplanid, vc_rectype,
vc_dealdate, vc_logid, vc_applaydate
from indata_log
where (region, com, vc_subno, int_yxplanid, vc_dissrv, nvl(vc_addinf, '-'), vc_startdate,
case when vc_enddate is null or vc_enddate > '20200101000000' then '20300101000000'
else to_char(to_date(vc_enddate, 'yyyymmddhh24miss')-1/24/60/60, 'yyyymmddhh24miss') end)
in (
select region, 'WX' com, vc_subno, int_yxplanid, vc_dissrv,trim(nvl(vc_addinf, '-')), vc_startdate, /*af_trim*/
case when vc_enddate is null or vc_enddate > '20200101000000' then '20300101000000'
else to_char(to_date(vc_enddate, 'yyyymmddhh24miss')-1/24/60/60, 'yyyymmddhh24miss') end
from indata_log
where com = 'WX'
minus
select region, 'WX' com, vc_subno, int_yxplanid, vc_dissrv, vc_addinf, vc_startdate,
case when vc_enddate > '20200101000000' then '20300101000000' else vc_enddate end
from indata_log
where com = 'CA' and vc_source = '1' )
order by region, vc_applaydate);
------以上sql语句中有distinct的地方,建议修改成group by形式,避免产生不必要的排序。以上脚本如何在不改变提数结果及不改变字段和值的前提进行优化呢。
select to_char(a.applyoid) as recoid, '0' as subsid, a.privsetid as privid, c.disccode, d.billingnbr as affixinfo,
'BILLNBR' AS affixtype, a.startdate, a.enddate, to_char(a.grpsubsid) grpsubsid, 'D' as idu,e.recdate, '' as applyoid,
to_char(a.canceloid) as canceloid, b.memservnumber as servnumber, '0' as custid
from dhw_yxfn a, dhw_jtcy b, yxfn_djjh c, jtw_jtyh d,clrz e
where e.oid in (select oid from clrz where groupid in
(select groupid from clrz where oid in
(select recoid from group_member_recdetail where idu ='D' and recdate between :STARTDATE and :ENDDATE and memtype in (1,7))
and recdate between :STARTDATE and :ENDDATE )
and recdefid ='ChangeProduct')
and a.grpsubsmemoid = b.oid and a.region =%s/*and e.recdate between :STARTDATE and :ENDDATE*/
and a.privsetid = c.yxplanid and b.groupoid = d.subsid and a.canceloid=e.oid,esRegion.GetData());
--------以上语句如何在不改变提数结果及不改变字段和值的前提进行优化呢,偶优化了后,20分钟还是提不出数据,请高人指点。
, a.com
, a.vc_subno
, a.vc_dissrv
, a.vc_startdate
, a.vc_enddate
, a.vc_addinf
, a.int_yxplanid
, a.vc_rectype
, a.vc_dealdate
, a.vc_logid
, a.vc_applaydate
from indata_log a
(select b.region
, b.com
, b.vc_subno
, b.int_yxplanid
, b.vc_dissrv
, trim(nvl(b.vc_addinf, '-')) vc_addinf
, b.vc_startdate
, case when b.vc_enddate is null or b.vc_enddate >= '20200101000001'
then '20300101000000'
else to_char(to_number(b.vc_enddate) - 1)
end vc_enddate
from indata_log b
where b.com = 'WX'
minus
select c.region
, 'WX' com
, c.vc_subno
, c.int_yxplanid
, c.vc_dissrv
, c.vc_addinf
, c.vc_startdate
, case when c.vc_enddate >= '20200101000001'
then '20300101000000'
else c.vc_enddate
end
from indata_log c
where c.com = 'CA'
and c.vc_source = '1'
) d
where a.region = d.region
and a.com = d.com
and a.vc_subno = d.vc_subno
and a.int_yxplanid = d.int_yxplanid
and a.vc_dissrv = d.vc_dissrv
and a.vc_startdate = d.vc_startdate
and nvl(a.vc_addinf, '-') = d.vc_addinf
and (case when a.vc_enddate is null or a.vc_enddate >= '20200101000001'
then '20300101000000'
else to_char(to_number(a.vc_enddate) - 1)
end
) = d.vc_enddate
order by
a.region
, a.vc_applaydate; 在region,com,vc_subno,int_yxplanid,vc_dissrv,vc_startdate加联合索引
, '0' subsid
, a.privsetid privid
, c.disccode
, d.billingnbr affixinfo
, 'BILLNBR' affixtype
, a.startdate
, a.enddate
, to_char(a.grpsubsid) grpsubsid
, 'D' idu
, e.recdate
, '' applyoid
, to_char(a.canceloid) canceloid
, b.memservnumber servnumber
, '0' custid
from dhw_yxfn a
, dhw_jtcy b
, yxfn_djjh c
, jtw_jtyh d
, clrz e
where exists( select 1
from clrz t
,(select f.groupid
from clrz f
, group_member_recdetail g
where f.oid = g.recoid
and g.memtype in (1,7)
and g.recdate between :STARTDATE
and :ENDDATE
and f.recdate between :STARTDATE
and :ENDDATE
and g.idu = 'D'
) m
where t.groupid = m.groupid
and t.oid = e.oid
and t.recdefid ='ChangeProduct'
)
and a.grpsubsmemoid = b.oid
and a.privsetid = c.yxplanid
and b.groupoid = d.subsid
and a.canceloid = e.oid
and a.region = %s/*and e.recdate between :STARTDATE and :ENDDATE*/;
这是第二部分,远非完美,这两篇抛砖引玉吧,csdn高人如云,后面肯定有更好的方案。一个小建议哦,注意下编码风格,嵌套表最好不要超过三层。
2.换种方式实现排序功能(索引)。