兩層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%。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-69147/，如需轉載，請註明出處，否則將追究法律責任。