Oracle Sql优化之Merge 改写优化Update

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle Sql优化之Merge 改写优化Update，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2360字，纯文字阅读大概需要4分钟。

内容图文

table1 f set f.ljjine1= (select nvl(sum(nvl(b.jine1,0)),0) from table1 b where b.kjqj<=f.kjqj and b.gs=f.gs and b.bm=f.bm and b.yw=f.yw and b.currency=f.currency and substr(b.kjqj,1,4)=substr(f.kjqj,1,4)), f.jine2 = (select nvl(sum(nvl(e.jine1,0)),0) from table2 e where e.kjqj=f.kjqj=e.gs=f.gs and e.bm=f.bm and e.yw= f.yw), f.ljjine2 = (select nvl(sum(nvl(e.jine1,0)),0) from table2 e where e.kjqj<=f.kjqj and e.gs=f.gs and e.bm=f.bm and e.yw=f.yw and substr(e.kjqj,1,4)=substr(f.kjqj,1,4)) where substr(f.kjqj,1,4)= extract(year from sysdate)

2.分析语句:

a.第一个子查询除了等值条件,还有一个 “b.kjqj<=f.kjqj”非等值比较,因此这是一个累加,需要采用分析函数

b.第二个子查询有sum聚合函数,因此要把关联条件放入group by中,分组汇总

c.第三个子查询与第二个类似,只是等值条件改成不等值条件,所以要采用分析函数

3.子查询改写

第一个子查询改写如下

select b.rowid as rid ,sum(b.jine1) over (partition by b.gs,b.bm,b.yw,b.curreny order by b.kjqj) as ljjine1 
from table1 b where substr(b.kjqj,1,4) = extract(year from sysdate)

第二个子查询改写,把关联列放到Select和group by后面

select e.gs,e.bm,e.yw,e.kjqj,sum(jine1) as jine2 
from table2 e
where substr(e.kjqj,1,4)=extract(year from sysdate)
group by e.gs,e.bm,e.yw,e.kjqj

第三个子查询,可以在第二次子查询的基础上调用一次分析函数进行累加处理

select e.gs,e.bm,e.yw,e.kjqj,
sum(e.jine2) over(partition by e.gs,e.bm,e.yw order by e.kjqj) as ljjine2
from
 (select e.gs,e.bm,e.yw,e.kjqj,sum(jine1) as jine2 
from table2 e
where substr(e.kjqj,1,4)=extract(year from sysdate)
group by e.gs,e.bm,e.yw,e.kjqj) e

4.Merge改写的最终结果如下

Merge into table1 f
using(select b.rowid as rid ,sum(b.jine1) over (partition by b.gs,b.bm,b.yw,b.curreny order by b.kjqj) as ljjine1 
from table1 b  left join (
    select e.gs,e.bm,e.yw,e.kjqj,
   sum(e.jine2) over(partition by e.gs,e.bm,e.yw order by e.kjqj) as ljjine2
  from
   (select e.gs,e.bm,e.yw,e.kjqj,sum(jine1) as jine2 
   from table2 e
   where substr(e.kjqj,1,4)=extract(year from sysdate)
   group by e.gs,e.bm,e.yw,e.kjqj) e
) e on(b.gs=e.gs and b.bm=e.bm and b.yw=e.yw and b.kjqj=e.kjqj)
where substr(b.kjqj,1,4) = extract(year from sysdate)) b on (f.rowid=b.rid)
when matched then
   update set
    f.ljjine1= nvl(b.ljjine1,0),
    f.ljjine2=nvl(b.ljjine2,0),
    f.jine2 = nvl(b.jine2,,0)

Oracle Sql优化之Merge 改写优化Update

标签：

本文系统来源：http://www.cnblogs.com/zhulongchao/p/4572697.html

内容总结

以上是互联网集市为您收集整理的Oracle Sql优化之Merge 改写优化Update全部内容，希望文章能够帮你解决Oracle Sql优化之Merge 改写优化Update所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---