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时间
占用时间
结论
1
2275
362
并行度高，但最耗CPU资源
2
1622
416
并行度中等，最节省CPU资源（貌似是折中的选择）
3
1811
507
并行度最低，消耗CPU资源中等