针对union all的场景,总体思想是建立临时表将各个sql结果放入临时表中,最后查询临时表统一输出,下面举例介绍几个项目遇到的union all问题。
减少集群层临时表,变成节点即可运行,如下面某项目语句,2个union的条件加在一起,就是不要条件即可。然后再将不必要的外部嵌套去掉,性能提升4倍。
原SQL:
SELECT AAC001,
Sum(NORMALLOCALCOLLECTMONTH) NORMALLOCALCOLLECTMONTH,
Sum(NORMALOTHERCOLLECTMONTH) NORMALOTHERCOLLECTMONTH,
Sum(NORMALLOCAL_BEFORE92) NORMALLOCAL_BEFORE92,
Sum(NORMALOTHER_BEFORE92) NORMALOTHER_BEFORE92
FROM (SELECT AAC001,
Nvl(Sum(NORMALLOCALCOLLECTMONTH), 0)
- Nvl(Sum(OTHERLOCALCOLLECTMONTH), 0) NORMALLOCALCOLLECTMONTH,
Nvl(Sum(NORMALOTHERCOLLECTMONTH), 0) - Nvl(Sum(OTHEROTHERCOLLECTMONTH), 0) NORMALOTHERCOLLECTMONTH,
0 NORMALLOCAL_BEFORE92,
0 NORMALOTHER_BEFORE92
FROM (SELECT AAC001,
( CASE
WHEN Nvl(BAE181, ‘0’) != ‘1’
AND AAA115 = ‘10’ THEN Nvl(BAE089, 0)
ELSE 0
END ) NORMALLOCALCOLLECTMONTH,
( CASE
W