达梦数据库sql优化实践11

中国RS项目sql优化，原sql语句
SELECT MD.Y,MAX(MD.IMONTH) IMONTH,MAX(MD.IYMD) IYMD,MAX(APCITY.table3INCE) table3INCE,SUM(ICOUNT) ICOUNT,SUM(RSUCC) RSUCC,SUM(RFAIL1) RFAIL1,SUM(RFAIL2) RFAIL2,SUM(RNRET) RNRET
FROM MT_table1MD
LEFT JOIN (SELECT DISTINCT table2CODE,table3INCE FROM A_table3INCECITY) APCITY
ON MD.MOBILEtable2=APCITY.table2CODE
WHERE MD.Y = 2020
GROUP BY MD.Y,APCITY.table3INCE HAVING SUM(MD.ICOUNT)-SUM(MD.RFAIL1)>0

执行时间70秒。
经过与客户沟通，为报表业务，sql涉及的表每天凌晨3点_{6点进行更新，6点}第二天3点数据保持不变，因此考虑使用预处理物化视图进行优化
经过沟通，sql涉及的表每天3点至6点更新数据，6点至下一天3点没有任何数据变动，具备预加工优化的条件
create materialized view “EMP”.“RSSQL11”
/视图的列名, 格式为(<列名>{, <列名>}),此处为可选/
storage(initial 1, next 1, minextents 1, fillfactor 0)
refresh on demand with primary key complete start with datetime’2021-11-16 10:37:18’ next to_date(TO_CHAR(TRUNC(SYSDATE+1),‘YYYYMMDD’)||‘070000’,‘yyyymmddhh24miss’)
as
/<查询说明>/
SELECT
MD.Y ,
MAX(MD.IMONTH) IMONTH ,
MAX(MD.IYMD) IYMD ,
MAX(APCITY.table3INCE) table3INCE,
SUM(ICOUNT) ICOUNT ,
SUM(RSUCC) RSUCC ,
SUM(RFAIL1) RFAIL1 ,
SUM(RFAIL2) RFAIL2 ,
SUM(RNRET) RNRET
FROM
EMP.MT_table1MD
LEFT JOIN
(
SELECT DISTINCT table2CODE, table3INCE FROM EMP.A_table3INCECITY
)
APCITY
ON
MD.MOBILEtable2=APCITY.table2CODE

GROUP BY
MD.Y,
APCITY.table3INCE
HAVING
SUM(MD.ICOUNT)-SUM(MD.RFAIL1)>0;
comment on view “EMP”.“RSSQL11” is ‘区域统计报表 - 年报表1’;

select * from emp.rssql11 where y=2020;
优化后，执行时间降低到0.02秒

社区地址：https://eco.dameng.com