Oracle sql最佳化之分析函數最佳化標量子查詢

標籤：

待最佳化語句如下

select a.code as code, a.m_code as m_code,a.stktype as f_stype,a.e_year as e_year,          b.sname as sname,a.c_date as c_date,to_char(sysdate,‘YYYYMMDD‘) as createtime,          to_char(sysdate,‘YYYYMMDD‘) as updatetime,         (select sum(valuef2) from a t where t.code=a.code and t.c_date between to_char(to_date(a.c_date,‘YYYYMMDD‘)-180,‘YYYYMMDD‘) and a.c_date and t.e_year=a.e_year) e70115_70011,          (select sum(valuef1) from a t where t.code=a.code and t.c_date between to_char(to_date(a.c_date,‘YYYYMMDD‘)-180,‘YYYYMMDD‘) and a.c_date and t.e_year=a.e_year) e70104_70011,          (select sum(valuef6) from a t where t.code=a.code and t.c_date between to_char(to_date(a.c_date,‘YYYYMMDD‘)-180,‘YYYYMMDD‘) and a.c_date and t.e_year=a.e_year) e70126_70011,         (select sum(valuef2) from a t where t.code=a.code and t.c_date between to_char(to_date(a.c_date,‘YYYYMMDD‘)-180,‘YYYYMMDD‘) and a.c_date and t.e_year=a.e_year) e70131_70011,        ‘-‘ as f_unitfrom a,b@link bwhere a.code = b.codeand b.stype=2 and b.status=1 and c_date>to_char(sysdate-3,‘YYYYMMDD‘)

首先分析下標量子查詢中的過濾條件:

t.c_date between to_char(to_date(a.c_date,‘YYYYMMDD‘)-180,‘YYYYMMDD‘) and a.c_date

該語句的目標實現c_date 180天內的資料匯總，因此可以分析函數表示為

order by to_date(c_date,‘YYYYMMDD‘) range between 180 preceding current row

標量子查詢的語句可改寫為

sum(valuef2) over(partition by a.code,a.year order by to_date(a.c_date,‘YYYYMMDD‘) range between 180 preceding current row)

而我們只需要三天內的資料,所以加上case判斷

case   when a.c_date>to_char(sysdate-3,‘YYYYMMDD‘) then   sum(valuef2) over(partition by a.code,a.year order by to_date(a.c_date,‘YYYYMMDD‘) range between 180   preceding current row)end

最終整體語句可改寫為

select A.*,b.sname as sname,to_char(sysdate,‘YYYYMMDD‘) as createtime,to_char(sysdate,‘YYYYMMDD‘) as updatetime from(select a.code as code,a.m_code as m_code, a.stktype as f_stype,a.e_year as e_year, a.c_date as c_date,case   when a.c_date>to_char(sysdate-3,‘YYYYMMDD‘) then   sum(valuef2) over(partition by a.code,a.year order by to_date(a.c_date,‘YYYYMMDD‘) range between 180   preceding current row)end as f70115_70011,case   when a.c_date>to_char(sysdate-3,‘YYYYMMDD‘) then   sum(valuef1) over(partition by a.code,a.year order by to_date(a.c_date,‘YYYYMMDD‘) range between 180   preceding current row)end as f70104_70011,case   when a.c_date>to_char(sysdate-3,‘YYYYMMDD‘) then   sum(valuef6) over(partition by a.code,a.year order by to_date(a.c_date,‘YYYYMMDD‘) range between 180   preceding current row)end as f70126_70011,case   when a.c_date>to_char(sysdate-3,‘YYYYMMDD‘) then   sum(valuef5) over(partition by a.code,a.year order by to_date(a.c_date,‘YYYYMMDD‘) range between 180   preceding current row)end as f70131_70011,‘-‘ as f_unitfrom a where a.c_date>= to_char(sysdate-3-180,‘YYYYMMDD‘) ---縮小資料區間) A inner join b@link B on(A.code=B.code) where B.stype=2 and B.status=1 and A.c_date>=to_char(sysdate-3,‘YYYYMMDD‘)

隨著資料量的增加該最佳化的效率越明顯

Oracle sql最佳化之分析函數最佳化標量子查詢