Analysis function optimization of Oracle SQL optimization scalar quantum query

Source: Internet
Author: User
Tags scalar

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.