Analysis function optimization of Oracle SQL optimization scalar quantum query

The statement to be optimized is as follows

SelectA.code asCode, A.m_code asM_code,a.stktype asF_stype,a.e_year asE_year, B.sname asSname,a.c_date asC_date,to_char (Sysdate,'YYYYMMDD') asCreatetime, To_char (Sysdate,'YYYYMMDD') asUpdateTime, (Select sum(VALUEF2) fromA TwhereT.code=A.code andT.c_datebetweenTo_char (To_date (A.c_date,'YYYYMMDD')- the,'YYYYMMDD') andA.c_date andT.e_year=a.e_year) e70115_70011, (Select sum(VALUEF1) fromC 6whereT.code=A.code andT.c_datebetweenTo_char (To_date (A.c_date,'YYYYMMDD')- the,'YYYYMMDD') andA.c_date andT.e_year=a.e_year) e70104_70011, (Select sum(VALUEF6) fromA TwhereT.code=A.code andT.c_datebetweenTo_char (To_date (A.c_date,'YYYYMMDD')- the,'YYYYMMDD') andA.c_date andT.e_year=a.e_year) e70126_70011, (Select sum(VALUEF2) fromA TwhereT.code=A.code andT.c_datebetweenTo_char (To_date (A.c_date,'YYYYMMDD')- the,'YYYYMMDD') andA.c_date andT.e_year=a.e_year) e70131_70011,'-'  asF_unit fromA, b@linkbwhereA.code=B.code andB.stype=2  andB.status=1  andC_date>To_char (sysdate-3,'YYYYMMDD')

First, the filter conditions in the subscript quantum query are analyzed:

T.c_date between to_char (To_date (a.c_date,'YYYYMMDD')- ' YYYYMMDD ') and A.c_date

The target implementation of the statement c_date the data rollup within 180 days, so you can parse the function as

Order by To_date (c_date, ' YYYYMMDD ') range between + preceding current row

The statement of a scalar subquery can be rewritten as

sum  Over  by A.code,a.Yearorder by To_date (a.c_date,'YYYYMMDD'  between current row)

And we only need data within three days, so add case judgment

 Case    whenA.c_date>To_char (sysdate-3,'YYYYMMDD')  Then   sum(VALUEF2) Over(Partition byA.code,a. Year Order  byTo_date (A.c_date,'YYYYMMDD') Rangebetween  thePreceding Currentrow)End

The final overall statement can be rewritten as

SelectA.*, B.sname asSname,to_char (Sysdate,'YYYYMMDD') asCreatetime,to_char (Sysdate,'YYYYMMDD') asUpdateTime from(SelectA.code asCode,a.m_code asM_code, A.stktype asF_stype,a.e_year asE_year, A.c_date asC_date, Case    whenA.c_date>To_char (sysdate-3,'YYYYMMDD')  Then   sum(VALUEF2) Over(Partition byA.code,a. Year Order  byTo_date (A.c_date,'YYYYMMDD') Rangebetween  thePreceding Currentrow)End  asf70115_70011, Case    whenA.c_date>To_char (sysdate-3,'YYYYMMDD')  Then   sum(VALUEF1) Over(Partition byA.code,a. Year Order  byTo_date (A.c_date,'YYYYMMDD') Rangebetween  thePreceding Currentrow)End  asf70104_70011, Case    whenA.c_date>To_char (sysdate-3,'YYYYMMDD')  Then   sum(VALUEF6) Over(Partition byA.code,a. Year Order  byTo_date (A.c_date,'YYYYMMDD') Rangebetween  thePreceding Currentrow)End  asf70126_70011, Case    whenA.c_date>To_char (sysdate-3,'YYYYMMDD')  Then   sum(VALUEF5) Over(Partition byA.code,a. Year Order  byTo_date (A.c_date,'YYYYMMDD') Rangebetween  thePreceding Currentrow)End  asf70131_70011,'-'  asF_unit fromAwhereA.c_date>=To_char (sysdate-3- the,'YYYYMMDD')---Narrow the data range) AInner JoinB@linkB on(A.code=B.code)whereB.stype=2  andB.status=1  andA.c_date>=To_char (sysdate-3,'YYYYMMDD')

As the amount of data increases, the efficiency of the optimization is more pronounced

Analytic function optimization of Oracle SQL optimization scalar quantum query