SUM与GROUP BY语句的优化

一.SUM与GROUP BY语句的优化：

1.原语句为：
SELECT IID.INVENTORY_ITEM_ID, SUM(IID.AVAILABLE_TO_PROMISE_DIFF), SUM(IID.QUANTITY_ON_HAND_DIFF), SUM(IID.ACCOUNTING_QUANTITY_DIFF)
FROM BOSENT.INVENTORY_ITEM_DETAIL IID
WHERE ((IID.INVENTORY_ITEM_ID = '?'))
GROUP BY IID.INVENTORY_ITEM_ID

SQL语句中使用GROUP BY的原因是select子句中有INVENTORY_ITEM_ID这个字段。

分析上面的SQL语句，INVENTORY_ITEM_ID作为WHERE子句中的条件，它的值是已知的，无需从SQL中查询出来，所以可以把这个字段从select子句中去掉，同进去除GROUP BY子句。SQL可改写为：
SELECT SUM(IID.AVAILABLE_TO_PROMISE_DIFF), SUM(IID.QUANTITY_ON_HAND_DIFF), SUM(IID.ACCOUNTING_QUANTITY_DIFF)
FROM BOSENT.INVENTORY_ITEM_DETAIL IID
WHERE ((IID.INVENTORY_ITEM_ID = '?'))

2.执行计划
(1)优化前SQL语句的执行计划 (使用160001代替?的值，目的是看执行计划）
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 8008 (1)| 00:01:37 |
| 1 | SORT GROUP BY NOSORT| | 1 | 19 | 8008 (1)| 00:01:37 |
*| 2 | TABLE ACCESS FULL | INVENTORY_ITEM_DETAIL | 670K| 12M| 8008 (1)| 00:01:37 |
----------------------------------------------------------------------------------------------**

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("IID"."INVENTORY_ITEM_ID"='160001')

(2)优化前SQL语句的执行计划
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 8008 (1)| 00:01:37 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
|* 2 | TABLE ACCESS FULL| INVENTORY_ITEM_DETAIL | 670K| 12M| 8008 (1)| 00:01:37 |
--------------------------------------------------------------------------------------------****

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("IID"."INVENTORY_ITEM_ID"='160001')

3.执行计划分析
(1) SORT(AGGREGATE)
SORT(AGGREGATE)是指在没有GROUP BY的前提下，使用统计函数对全部数据对象进行运算时所显示出来的执行计划。在使用SUM、COUNT、MIN、MAX、AVG等统计函数时并不执行一般排序。而是在读取表中数据的同时以每一行数据为对象进行求和，AVG或者COUNT也同样是以所读取的每一行数据为对象进行反复计算的。
SORT AGGREGATE做为sort的option之一比较特殊，它并不做sort，SORT AGGREGATE作用于所有的data set上。

那么MIN和MAX是如何进行计算的呢？其方法与上面所介绍的基本相同。MIN的执行方法为在最开始将所读取的第一个值记录下来，然后将该值与下一个读取的值进行比较，如果比该值小则将其替换。在执行该统计操作时如果可以使用索引，则能够获得非常好的效果。在该执行计划中虽然显示的是索引全扫描，但实际上，仅仅会读取第一个索引块，之后并不继续进行扫描（因为索引是排序的，因此索引列值的最小值必然在索引的第一个块上）。而MAX则是仅仅读取最后一个索引块。想要获得这样的执行计划，不能使用WHERE和GROUP BY，当然还要求所要进行统计的列必须是索引的先行列（如果是组合索引，则该列必须位于最前面），并且在SELECT-List中不能添加其他任何额外的操作要求。

(2) SORT(GROUP BY)
该操作是将数据行向不同分组中聚集的操作，即依据查询语句中所使用的GROUP BY而进行的相关操作，为了进行分组就只能进行排序，因此所需分组的数据量越大则代价就越高。

如果想通过GROUP BY 将海量表分为上千个组，在这种对超大型表执行GROUP BY时，就可以明显地感觉到该操作所需要的代价。当所要排序的数据超过一定量时，其代价就会变得非常大，解决该问题的一个方法就是使用HASH(GROUP BY)。

(3) SORT(UNIQUE)
该操作是指把查询语句的输出结果变成唯一集合的过程。出现该排序的情况有两种，一种是使用了“DISTINCT”，另外一种是子查询以提供者角色向主查询提供其执行结果。

SELECT order_id,order_date
FROM orders
WHERE order_id in (SELECT order_id
FROM order_item
WHERE item_id = :b1
AND order_qty>100);
由于主查询的结果必须存在于子查询中，在这里必须要将作为“M”集合的子查询转换为不允许重复元素存在的“1”集合，所以执行了SORT(UNIQUE)操作。如果该子查询被放在主查询之后执行，则该排序执行计划就不会被显示出来，此时显示的是FILTER。

4.另一种不修改程序和SQL语句的优化方法

表INVENTORY_ITEM_DETAIL已有INVENTORY_ITEM_ID单个字段的索引，这个表共有26个字段，SUM只对其中三个字段进行统计，通过创建下面的一个复合索引，性能有大幅提高。
CREATE INDEX
I_INVENTORY_ITEM_DETAIL_XX
ON
INVENTORY_ITEM_DETAIL
(
INVENTORY_ITEM_ID,AVAILABLE_TO_PROMISE_DIFF,QUANTITY_ON_HAND_DIFF,ACCOUNTING_QUANTITY_DIFF
);

经验证测试，创建这个索引后，取款交易响应时间从4.5s下降到2.0s；存款交易从2.7s下降到1.6s。效果明显。（压力测试环境中表INVENTORY_ITEM_DETAIL的记录数是213万条记录。测试机器是虚拟机）

5.把第3，4种优化方法同时用上，性能更好。