Oracle SQL optimization Merge overrides optimized update

1. The statement to be rewritten is as follows

Updatetable1 FSetF.ljjine1=(SelectNvlsum(NVL (B.jine1,0)),0) fromTable1 bwhereB.kjqj<=F.kjqj andB.gs=F.gs andB.bm=F.bm andB.yw=F.yw andB.currency=F.currency andSUBSTR (B.KJQJ,1,4)=SUBSTR (F.KJQJ,1,4)), F.jine2=(SelectNvlsum(NVL (E.jine1,0)),0) fromTable2 EwhereE.kjqj=F.kjqj=E.gs=F.gs andE.bm=F.bm andE.yw=f.yw), F.ljjine2=(SelectNvlsum(NVL (E.jine1,0)),0) fromTable2 EwhereE.kjqj<=F.kjqj andE.gs=F.gs andE.bm=F.bm andE.yw=F.yw andSUBSTR (E.KJQJ,1,4)=SUBSTR (F.KJQJ,1,4))whereSUBSTR (F.KJQJ,1,4)=Extract Year  fromSysdate)

2. Analysis statement:

A. The first subquery, in addition to the equivalent condition, also has a "b.kjqj<=f.kjqj" non-equivalence comparison, so this is an additive that requires the use of analytic functions

B. The second subquery has a SUM aggregate function, so to put the association condition into group BY, the group summary

C. The third sub-query is similar to the second, except that the equivalent condition is changed to a non-equivalent condition, so the analytic function should be used

3. Sub-query rewriting

The first subquery is rewritten as follows

Select  as RID,sumover byorder by as from where substr (B.KJQJ,1,4= Extract ( year from Sysdate)

The second subquery overrides, placing the association columns behind select and GROUP by

Select e.gs,e.bm,e.yw,e.kjqj,sum as from table2 ewhere substr (E.KJQJ,1,4)=Extract ( year from sysdate) Group  by E.gs,e.bm,e.yw,e.kjqj

The third subquery, can be called on the basis of the second sub-query analysis function to accumulate processing

SelectE.gs,e.bm,e.yw,e.kjqj,sum(E.jine2) Over(Partition byE.gs,e.bm,e.ywOrder  byE.KJQJ) asLjjine2 from (SelectE.GS,E.BM,E.YW,E.KJQJ,sum(JINE1) asJine2 fromtable2 ewhereSUBSTR (E.KJQJ,1,4)=Extract Year  fromsysdate)Group  byE.GS,E.BM,E.YW,E.KJQJ) E

The final result of the 4.Merge rewrite is as follows

Merge intotable1 Fusing (SelectB.rowid asRID,sum(B.JINE1) Over(Partition byB.gs,b.bm,b.yw,b.currenyOrder  byB.KJQJ) asljjine1 fromTable1 b Left Join (    SelectE.gs,e.bm,e.yw,e.kjqj,sum(E.jine2) Over(Partition byE.gs,e.bm,e.ywOrder  byE.KJQJ) asLjjine2 from   (SelectE.GS,E.BM,E.YW,E.KJQJ,sum(JINE1) asJine2 fromtable2 ewhereSUBSTR (E.KJQJ,1,4)=Extract Year  fromsysdate)Group  byE.GS,E.BM,E.YW,E.KJQJ) e) e on(B.gs=E.gs andB.bm=E.bm andB.yw=E.yw andB.kjqj=e.kjqj)whereSUBSTR (B.KJQJ,1,4)=Extract Year  fromSysdate)) b on(F.rowid=B.rid) whenMatched Then   Update Setf.ljjine1=NVL (B.ljjine1,0), F.ljjine2=NVL (B.ljjine2,0), F.jine2=NVL (B.jine2,,0)

Oracle SQL optimization Merge overrides optimized update