UNION和GROUP BY连用 导致的无法谓词推入问题

问题概述

在分析客户环境的一条SQL时，发现了无法做谓词推入的现象。造成视图中的大表访问比较低效。故此对案例做了进一步分析及测试。以确定问题原因。

问题SQL：

SELECT SUM("A2"."PREM")
  FROM ((SELECT "A5"."AGENT_ID", SUM("A5"."PREM") "PREM"
           FROM QUERY_DES "A5"
          GROUP BY "A5"."AGENT_ID") 
          UNION ALL
        (SELECT "A4"."AGENT_ID", SUM("A4"."PREM") PREM
           FROM QUERY_OLD "A4"
          GROUP BY "A4"."AGENT_ID")) "A2",
       T_MAIN "A1"
 WHERE "A2"."AGENT_ID" = "A1"."AGENT_ID"
   AND "A1"."AGENT_CLASS" = 1
   AND "A1"."IDNUM" = 6;

SQL文本中包含通过UNION ALL合并的两个group by视图。视图与外部表通过AGENT_ID关联。

对应的执行计划如下：

其中主要的耗时见标红部分。视图中访问了大量的中间结果，最后聚合后与外部表关联。为此创建关联条件列索引。

create index IDX_QUERYDES_AGENT on QUERY_DES(AGENT_ID,PREM);

create index IDX_QUERYOLD_AGENT on QUERY_OLD(AGENT_ID,PREM);

这里奇怪的是，外部T_MAIN只有一行记录。且存在关联条件索引时，我们希望能将关联条件推入到视图内部，这样可以一定程度上降低视图中表访问的中间结果，来提升访问的效率。而本例中显然没有成功。

这里看到是一体机环境，才能达到1秒左右的执行时间，如果是传统的X86环境，上述大表的全表扫其执行效率明显会很差。

问题分析

这里我们期望看到的是谓词推入技术。而实际事与愿违，不论如何添加hint，都无法推入关联条件到A2视图内。总是通过全表扫描访问视图中的大表。造成性能问题。对比谓词推入的条件：

可以看到是满足视图中包含UNION/UNION ALL的。只是唯一要注意的是UNION ALL两部分都是单独的GROUP BY结构。是否是这里的原因，导致无法继续推入到视图内部表的呢？

这里分析10053事件，这里看到连接谓词推入被跳过，基本确定与GROUP BY代码块有关。

而视图合并CVM也是更不会考虑。

因此这里的UNION和GROUP BY连用，没有太有效的查询转换手段可以改善效率。

下面对SQL做一定程度的改写测试：

1. 去掉GROUP BY聚合

首先分析语句含义，GROUP BY只是为了获取每个AGENT_ID聚合后的SUM(PREM)值，而最外层也仅是对关联后的SUM(PREM)在做一次聚合。因此这里分析内部的SUM聚合完全就是可以省去的。去掉UNION ALL两部分的GROUP BY聚合。改写如下：

SELECT SUM("A2"."PREM")
  FROM ((SELECT "A5"."AGENT_ID", "A5"."PREM" FROM QUERY_DES "A5")
        UNION ALL
        (SELECT "A4"."AGENT_ID", "A4"."PREM" FROM QUERY_OLD "A4")) "A2",
       T_MAIN "A1"
 WHERE "A2"."AGENT_ID" = "A1"."AGENT_ID"
   AND "A1"."AGENT_CLASS" = 1
   AND "A1"."IDNUM" = 6;

针对本条查询的场景，完全可以等价的改写为上述写法。省去内层不必要的聚合的。

改写后的执行计划如下：

调整为了上面的UNION ALL写法，外层条件顺利推入到视图内部。通过关联条件降低了内层表访问的中间结果。执行效率有了较大改善。

2.与内层GROUP BY关联后再UNION

上一小节的改法，受到很多限制。只有本案例相对特殊的情况才能省去内部聚合。更多的情况内部聚合无法拆开。因此更为广泛的改法是将外部表分别与内部两个GROUP BY视图关联，最后再UNION合并。

SELECT SUM("PREM")
  from (select "A1"."AGENT_ID", a2.PREM
          FROM (SELECT "A5"."AGENT_ID", SUM("A5"."PREM") "PREM"
                  FROM QUERY_DES "A5"
                 GROUP BY "A5"."AGENT_ID") "A2",
               T_MAIN "A1"
         WHERE "A2"."AGENT_ID" = "A1"."AGENT_ID"
           AND "A1"."AGENT_CLASS" = 1
           AND "A1"."IDNUM" = 6
        union all
        select "A1"."AGENT_ID", a2.PREM
          FROM (SELECT "A4"."AGENT_ID", SUM("A4"."PREM") PREM
                  FROM QUERY_OLD "A4"
                 GROUP BY "A4"."AGENT_ID") "A2",
               T_MAIN "A1"
         WHERE "A2"."AGENT_ID" = "A1"."AGENT_ID"
           AND "A1"."AGENT_CLASS" = 1
           AND "A1"."IDNUM" = 6);

这样调整后，是利用了谓词推入条件的“视图定义中包含GROUP BY”。UNION后在外层求和与原始查询等价。

调整后的执行计划：

调整后，相当于两部分满足谓词推入的场景。优化器分别对两部分GROUP BY应用了谓词推入技术。传入关联条件以降低视图中的数据访问量，提升查询效率。

除此之外，GROUP BY视图也是满足复杂视图合并的场景的。因此本案例还可以有更多的查询转换手段。复杂视图合并。

SELECT SUM("PREM")
  from (select "A1"."AGENT_ID", a2.PREM
          FROM (SELECT /*+ merge */
                 "A5"."AGENT_ID", SUM("A5"."PREM") "PREM"
                  FROM QUERY_DES "A5"
                 GROUP BY "A5"."AGENT_ID") "A2",
               T_MAIN "A1"
         WHERE "A2"."AGENT_ID" = "A1"."AGENT_ID"
           AND "A1"."AGENT_CLASS" = 1
           AND "A1"."IDNUM" = 6
        union all
        select "A1"."AGENT_ID", a2.PREM
          FROM (SELECT /*+ merge */
                 "A4"."AGENT_ID", SUM("A4"."PREM") PREM
                  FROM QUERY_OLD "A4"
                 GROUP BY "A4"."AGENT_ID") "A2",
               T_MAIN "A1"
         WHERE "A2"."AGENT_ID" = "A1"."AGENT_ID"
           AND "A1"."AGENT_CLASS" = 1
           AND "A1"."IDNUM" = 6);

利用hint实现GROUP BY视图合并。调整后执行计划如下：

这样的写法还可以有视图合并手段可以选择，统一应用了关联条件实现内层表的过滤，提升了执行效率。

解决方案及总结

通过上一章节分析，针对UNION和GROUP BY视图连用的情况。无论是谓词推入还是视图合并，都无法进行。因此就需要一定程度的改写。

而我们分析：更有效的改写方法为通过外层表与内层视图关联后在UNION的方式。好处如下：

没有太多写法限制。不论最外层对聚合后的列做何种写法，都可以应用这种改写方式。

2.改写后性能更好。拆开后再UNION是先应用关联条件后立刻聚合。缩减数据量的同时执行效率更好。优于UNION两部分数据最后再聚合的方式。

3.有更多的查询转换手段。有视图合并和谓词推入两种转换手段。当不满足某一条件时还可以尝试另一种查询转换手段。更好的保证执行效率。

很多时候的无法谓词推入问题都与这类情况有关，可以利用外层表与内层视图关联后在UNION的方式来改写。还可以结合CTE写法（MATERIALIZE/INLINE），实现简化代码和提升性能的不同需求。