一条sql语句的优化,急待解决?

kerisyml 2011-07-11 10:07:14
每个都是去关联一个相应的远程数据库,执行完一个需要花费很长很长时间,有没有更好的,有优化的方法呢,
请高手来解决。。。非常谢谢。。。


insert into z_telanlyse(NLIST_SN,NHDXH,SHDLY,SDHHM,STHBS,SZBJBS,SDFHM,DHJRQ,NTHSC,SYHDD,SDFDD,SSBBH,SXQBH,SDQBH,NCJ_SN,SDFXM,SDFSFZH,NDFXXK,SDFHMHZ)
(select a.NLIST_SN,a.NHDXH,a.SHDLY,a.SDHHM,a.STHBS,a.SZBJBS,a.SDFHM,a.DHJRQ,a.NTHSC,a.SYHDD,a.SDFDD,a.SSBBH,a.SXQBH,a.SDQBH,a.NCJ_SN,g.sryxm dfxm,g.ssfzh dfsfzh,
decode(g.slllb,'1','21','2','20','3','20') dfxxk,decode(g.slllb,'1',h.sryxm||':'||h.sdhhm||':'||h.ssfzh) dfhmhz
from ( z_tellistmx a left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join z_address g on a.sdfhm=g.sdhhm and g.sdhhm not in ('10086','110')
left join z_teluser h on g.nuser_sn=h.nuser_sn )
where a.nlist_sn=p_nlistsn
union
select a.NLIST_SN,a.NHDXH,a.SHDLY,a.SDHHM,a.STHBS,a.SZBJBS,a.SDFHM,a.DHJRQ,a.NTHSC,a.SYHDD,a.SDFDD,a.SSBBH,a.SXQBH,a.SDQBH,a.NCJ_SN,
i.sryxm dfxm,i.ssfzh dfsfzh,'22' dfxxk,'' dfhmhz
from ( z_tellistmx a left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join z_teluser i on a.sdfhm=i.sdhhm )
where a.nlist_sn=p_nlistsn
union
select a.NLIST_SN,a.NHDXH,a.SHDLY,a.SDHHM,a.STHBS,a.SZBJBS,a.SDFHM,a.DHJRQ,a.NTHSC,a.SYHDD,a.SDFDD,a.SSBBH,a.SXQBH,a.SDQBH,a.NCJ_SN,
p.xm dfxm,p.gmsfzh dfsfzh,'2' dfxxk,'' dfhmhz
from ( z_tellistmx a left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_czzzrk p on a.sdfhm=p.lxdh )
where a.nlist_sn=p_nlistsn
union
select a.NLIST_SN,a.NHDXH,a.SHDLY,a.SDHHM,a.STHBS,a.SZBJBS,a.SDFHM,a.DHJRQ,a.NTHSC,a.SYHDD,a.SDFDD,a.SSBBH,a.SXQBH,a.SDQBH,a.NCJ_SN,
m.xm dfxm,m.gmsfhm dfsfzh,'1' dfxxk,'' dfhmhz
from ( z_tellistmx a left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_czczrk m on a.sdfhm=m.lxdh )
where a.nlist_sn=p_nlistsn
union
select a.NLIST_SN,a.NHDXH,a.SHDLY,a.SDHHM,a.STHBS,a.SZBJBS,a.SDFHM,a.DHJRQ,a.NTHSC,a.SYHDD,a.SDFDD,a.SSBBH,a.SXQBH,a.SDQBH,a.NCJ_SN,
n.xm dfxm,n.sfzmhm dfsfzh,'4' dfxxk,'' dfhmhz
from ( z_tellistmx a left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_czjsyxx n on a.sdfhm=n.lxdh )
where a.nlist_sn=p_nlistsn
union
select a.NLIST_SN,a.NHDXH,a.SHDLY,a.SDHHM,a.STHBS,a.SZBJBS,a.SDFHM,a.DHJRQ,a.NTHSC,a.SYHDD,a.SDFDD,a.SSBBH,a.SXQBH,a.SDQBH,a.NCJ_SN,
t.syr dfxm,t.sfzmhm dfsfzh,'5' dfxxk,'' dfhmhz
from ( z_tellistmx a left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_czjdcxx t on a.sdfhm=t.lxdh )
where a.nlist_sn=p_nlistsn
union
select a.NLIST_SN,a.NHDXH,a.SHDLY,a.SDHHM,a.STHBS,a.SZBJBS,a.SDFHM,a.DHJRQ,a.NTHSC,a.SYHDD,a.SDFDD,a.SSBBH,a.SXQBH,a.SDQBH,a.NCJ_SN,
t.bpcrxm dfxm,t.bpcrsfzhm dfsfzh,'14' dfxxk,'' dfhmhz
from ( z_tellistmx a left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_czzapcxx t on a.sdfhm=t.lxdh )
where a.nlist_sn=p_nlistsn
union
select a.NLIST_SN,a.NHDXH,a.SHDLY,a.SDHHM,a.STHBS,a.SZBJBS,a.SDFHM,a.DHJRQ,a.NTHSC,a.SYHDD,a.SDFDD,a.SSBBH,a.SXQBH,a.SDQBH,a.NCJ_SN,
t.r_name dfxm,t.r_sfzbh dfsfzh,'13' dfxxk,'' dfhmhz
from ( z_tellistmx a left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_czylcscyry t on a.sdfhm=t.r_sj )
where a.nlist_sn=p_nlistsn
union
select a.NLIST_SN,a.NHDXH,a.SHDLY,a.SDHHM,a.STHBS,a.SZBJBS,a.SDFHM,a.DHJRQ,a.NTHSC,a.SYHDD,a.SDFDD,a.SSBBH,a.SXQBH,a.SDQBH,a.NCJ_SN,
t.sxm dfxm,t.szjhm dfsfzh,'6' dfxxk,'' dfhmhz
from ( z_tellistmx a left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_czjdcwfry t on a.sdfhm=t.sdh )
where a.nlist_sn=p_nlistsn
union
select a.NLIST_SN,a.NHDXH,a.SHDLY,a.SDHHM,a.STHBS,a.SZBJBS,a.SDFHM,a.DHJRQ,a.NTHSC,a.SYHDD,a.SDFDD,a.SSBBH,a.SXQBH,a.SDQBH,a.NCJ_SN,
t.bjr dfxm,'' dfsfzh,'15' dfxxk,'' dfhmhz
from ( z_tellistmx a left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_czjcjxx t on a.sdfhm=t.lxdh and t.lxdh<>'110' )
where a.nlist_sn=p_nlistsn
union
select a.NLIST_SN,a.NHDXH,a.SHDLY,a.SDHHM,a.STHBS,a.SZBJBS,a.SDFHM,a.DHJRQ,a.NTHSC,a.SYHDD,a.SDFDD,a.SSBBH,a.SXQBH,a.SDQBH,a.NCJ_SN,
t.barxm dfxm,t.barzjhm dfsfzh,'12' dfxxk,'' dfhmhz
from ( z_tellistmx a left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_czajxx t on a.sdfhm=t.barlxfs and t.barlxfs<>'110' )
where a.nlist_sn=p_nlistsn

union
select a.NLIST_SN,a.NHDXH,a.SHDLY,a.SDHHM,a.STHBS,a.SZBJBS,a.SDFHM,a.DHJRQ,a.NTHSC,a.SYHDD,a.SDFDD,a.SSBBH,a.SXQBH,a.SDQBH,a.NCJ_SN,
t.xm dfxm,t.gmsfhm dfsfzh,'10' dfxxk,'' dfhmhz
from ( z_tellistmx a left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join view_rsryxx t on a.sdfhm=t.lxdh and t.lxdh not in ('110','10086') )
where a.nlist_sn=p_nlistsn

union
select a.NLIST_SN,a.NHDXH,a.SHDLY,a.SDHHM,a.STHBS,a.SZBJBS,a.SDFHM,a.DHJRQ,a.NTHSC,a.SYHDD,a.SDFDD,a.SSBBH,a.SXQBH,a.SDQBH,a.NCJ_SN,
t.lxjrxsm dfxm,'' dfsfzh,'24' dfxxk,h.xm||':'||h.lxdh||':'||h.gmsfhm dfhmhz
from ( z_tellistmx a left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_rsrytxl t on a.sdfhm=t.lxjrdh and t.lxjrdh not in ('110','10086')
left join view_rsryxx h on t.glrybh=h.rybh)
where a.nlist_sn=p_nlistsn
union
select a.NLIST_SN,a.NHDXH,a.SHDLY,a.SDHHM,a.STHBS,a.SZBJBS,a.SDFHM,a.DHJRQ,a.NTHSC,a.SYHDD,a.SDFDD,a.SSBBH,a.SXQBH,a.SDQBH,a.NCJ_SN,
t.xm dfxm,t.sfzh dfsfzh,'9' dfxxk,'' dfhmhz
from ( z_tellistmx a left join z_tellist b on a.sdhhm=b.sdhhm and a.nlist_sn=b.nlist_sn
inner join syn_czddcxx t on a.sdfhm=t.lxdh )
where a.nlist_sn=p_nlistsn

);
...全文
90 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
Kobayashi 2011-11-03
  • 打赏
  • 举报
回复
我的见意。。如果可以不使用UNION就不要使用。你还情愿多写一个插入语句。
而且我看到你的没有加事务。你试下加个事务。速度会快一点。
我本是朱 2011-11-02
  • 打赏
  • 举报
回复
路过,涨涨人气顺便接点分
记得union是要排序的,union all不需要
(这么长sql不怕临时表空间不足吗?)
kerisyml 2011-11-01
  • 打赏
  • 举报
回复
这么久没有人回应啊
kerisyml 2011-07-11
  • 打赏
  • 举报
回复
提示:该加的索引都加了啊,主要是看看语句的写法上还有什么更好的方法。

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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