union all和group by的顺序问题

通过三个实验，来看 union all 及 group by 不同的使用顺序对CPU时间及耗时的影响

1. 先Union All，再Group By

select inctmid ctmid,cnlid,inwhsid whsid,sum(qty) qty,goodsid 
from
(     select inctmid,cnlid,inwhsid,qty,goodsid 
       from DtrBill a,DtrDetail b
       where a.billno=b.billno 
       union all
       select inctmid,cnlid,inwhsid,-1*qty qty,goodsid 
       from DtrBillRet a,DtrDetailRet b
       where a.billno=b.billno
)a
group by inctmid,cnlid,inwhsid,goodsid

2. 分别Group By，再Union All，再Group By

select inctmid ctmid,cnlid,inwhsid whsid,sum(qty) qty,goodsid 
from
(    select inctmid,cnlid,inwhsid,sum(qty) qty,goodsid 
      from DtrBill a,DtrDetail b
      where a.billno=b.billno 
      group by inctmid,cnlid,inwhsid,goodsid
      union all
     select inctmid,cnlid,inwhsid,sum(-1*qty) qty,goodsid 
     from DtrBillRet a,DtrDetailRet b
     where a.billno=b.billno
     group by inctmid,cnlid,inwhsid,goodsid
)a
group by inctmid,cnlid,inwhsid,goodsid

3. 先Group By，再Union

select inctmid,cnlid,inwhsid,sum(qty) qty,goodsid 
from DtrBill a,DtrDetail b
where a.billno=b.billno 
group by inctmid,cnlid,inwhsid,goodsid
union 
select inctmid,cnlid,inwhsid,sum(-1*qty) qty,goodsid 
from DtrBillRet a,DtrDetailRet b
where a.billno=b.billno
group by inctmid,cnlid,inwhsid,goodsid

4. 执行计划上的不同：实验 2,3 多了两个分支上的Hash匹配操作

5. 实验结果及结论

方式

CPU时间

占用时间

结论

2275

362

并行度高，但最耗CPU资源

1622

416

并行度中等，最节省CPU资源（貌似是折中的选择）

1811

507

并行度最低，消耗CPU资源中等