GaussDB(DWS)性能调优：Oracle关联更新SQL语句优化

Oracle中习惯使用如下的关联查询实现表的更新动作

UPDATE boss_t_glbalance t1 SET(t1.aviamt, t1.lasttime) =  
(
    SELECT
        t1.aviamt + t2.amt,
        '2020-03-25 11:15:17' 
    FROM 
    (
        SELECT 
            a.toctrlid,sum(a.amt) amt,
            a.province,a.year
        FROM  FASP_TMP_GLCTRL122299 a
        WHERE a.isexistfromctrlid = 1 AND a.toctrlid IS NOT NULL AND a.month <= extract(month FROM now())
        GROUP BY a.toctrlid,a.province,a.year 
    ) t2 
    WHERE t2.toctrlid = t1.sumguid AND t2.province=t1.province AND t2.year=t1.year
)  
WHERE EXISTS 
(
    SELECT 
        1 
    FROM
    (
        SELECT 
            sum(a.amt) AS amt,a.toctrlid,a.province,a.year
        FROM  FASP_TMP_GLCTRL122299 a
        WHERE a.isexistfromctrlid = 1 AND a.toctrlid is not null AND a.month <= extract(month FROM now())
        GROUP BY a.toctrlid,a.province,a.year 
    ) t3 
    WHERE t1.sumguid = t3.toctrlid  AND t1.province=t3.province AND t1.year=t3.year 
)
;

这种语句中存在两个类似的子查询动作，会形成冗余计算，导致执行性能劣化。在DWS中可以使用UPDATE...FROM...语法进行关联更新，优化掉其中的一个子查询动作

UPDATE boss_t_glbalance t1 SET (t1.aviamt,t1.lasttime) =  (t1.aviamt + t2.amt, '2020-03-25 11:15:17')
FROM
(
    SELECT 
        sum(a.amt) AS amt, a.toctrlid,a.province,a.year
    FROM  FASP_TMP_GLCTRL122299 a
    WHERE a.isexistfromctrlid = 1 AND a.toctrlid IS NOT NULL AND a.month <= extract(month FROM sysdate) 
    GROUP BY a.toctrlid,a.province,a.year 
) t2 
WHERE t2.toctrlid = t1.sumguid AND t2.province=t1.province AND t2.year=t1.year
;

如上的SQL语句看起来更清晰，并且减少了一次子查询动作，执行性能更好