【转载】 yangtingkun: 两层GROUP BY效率反而比一层GROUP BY效率高——SQL优化之Everything is possible

在一般的优化思想中，都是要尽可能减少嵌套的层数，减少不必要的操作。但是今天发现有的时候并非实际情况却并非如此。

看下面的例子：

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

表已创建。

SQL> INSERT INTO T SELECT * FROM T;

已创建32774行。

SQL> INSERT INTO T SELECT * FROM T;

已创建65548行。

SQL> INSERT INTO T SELECT * FROM T;

已创建131096行。

SQL> INSERT INTO T SELECT * FROM T;

已创建262192行。

SQL> COMMIT;

提交完成。

SQL> SET AUTOT TRACE
SQL> SET TIMING ON
SQL> COL PLAN_PLUS_EXP FORMAT A80
SQL> SET LINES 110
SQL> SELECT OBJECT_TYPE, COUNT(DISTINCT CREATED)
2 FROM T
3 GROUP BY OBJECT_TYPE;

已选择34行。

已用时间: 00: 00: 01.31

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'T'

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3549 consistent gets
108 physical reads
0 redo size
1099 bytes sent via SQL*Net to client
386 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
34 rows processed

SQL> SELECT OBJECT_TYPE, COUNT(CREATED)
2 FROM
3 (
4 SELECT OBJECT_TYPE, CREATED
5 FROM T
6 GROUP BY OBJECT_TYPE, CREATED
7 )
8 GROUP BY OBJECT_TYPE;

已选择34行。

已用时间: 00: 00: 00.79

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 VIEW
3 2 SORT (GROUP BY)
4 3 TABLE ACCESS (FULL) OF 'T'

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3549 consistent gets
109 physical reads
0 redo size
1091 bytes sent via SQL*Net to client
386 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
34 rows processed

从统计信息上看，两个SQL的统计信息完全一致。而从执行计划上看，前者只有一次GROUP BY操作，而后者需要GROUP BY两次，嵌套的层数也比前者多。

但是从执行时间上看，却是后者比前者的速度更快。所用时间只有前者的60%。

开始我也很不理解，但是仔细考虑了一下，认为可能的主要原因是COUNT(DISTINCT CREATED)操作比较费时。

对于第二个查询而言，虽然需要GROUP BY操作两次，但是由于第二次GROUP BY的字段OBJECT_TYPE就是第一次GROUP BY字段中的一部分，数据是按照顺序从第一步得到的，这里的GROUP BY操作中最费时的排序其实基本上没有去做，整个GROUP BY就是一个计算CREATED个数的操作。

而对于第一个查询，虽然只有一层，且查询只对OBJECT_TYPE进行了GROUP BY，但是COUNT(DISTINCT CREATED)操作并不是简单的计数，而是要统计不同的CREATED值。虽然从统计信息没有看到排序操作，但是由于要计算不同的CREATED值，Oracle肯定需要保存并比较CREATED的值，怀疑这个操作比GROUP BY操作的效率要低，这就是造成了两个GROUP BY效率比一个GROUP BY效率还高的原因。

这个问题同时也说明一个问题，虽然99%的情况下，通过逻辑读来判断SQL执行效率都是准确的，但是这个SQL给出了一个反例。在逻辑读和物理读都相同的情况下，SQL的执行效率还是可能有很大的差别了。这个例子中的差别无论从统计信息还是从执行计划中都是无法发现的。

下面看一个更加极端的例子：

SQL> SELECT OBJECT_TYPE, COUNT(DISTINCT CREATED) CREATED, COUNT(DISTINCT STATUS) STATUS
2 FROM T
3 GROUP BY OBJECT_TYPE;

已选择34行。

已用时间: 00: 00: 01.78

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'T'

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3549 consistent gets
0 physical reads
0 redo size
1217 bytes sent via SQL*Net to client
386 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
34 rows processed

SQL> SELECT B.OBJECT_TYPE, CREATED, STATUS
2 FROM
3 (
4 SELECT OBJECT_TYPE, COUNT(CREATED) CREATED
5 FROM
6 (
7 SELECT OBJECT_TYPE, CREATED
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 )
11 GROUP BY OBJECT_TYPE
12 ) B,
13 (
14 SELECT OBJECT_TYPE, COUNT(STATUS) STATUS
15 FROM
16 (
17 SELECT OBJECT_TYPE, STATUS
18 FROM T
19 GROUP BY OBJECT_TYPE, STATUS
20 )
21 GROUP BY OBJECT_TYPE
22 ) C
23 WHERE B.OBJECT_TYPE = C.OBJECT_TYPE;

已选择34行。

已用时间: 00: 00: 01.14

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 VIEW
3 2 SORT (GROUP BY)
4 3 VIEW
5 4 SORT (GROUP BY)
6 5 TABLE ACCESS (FULL) OF 'T'
7 1 SORT (JOIN)
8 7 VIEW
9 8 SORT (GROUP BY)
10 9 VIEW
11 10 SORT (GROUP BY)
12 11 TABLE ACCESS (FULL) OF 'T'

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7098 consistent gets
0 physical reads
0 redo size
1217 bytes sent via SQL*Net to client
386 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
34 rows processed

这两个SQL是等价的，但是第二个SQL的统计信息中逻辑读是第一个的二倍，排序次数是第一个SQL的5倍。从执行计划上看，更是比第一个SQL不知道复杂多少倍。但是运行时间却只有第一个SQL的64%。