增加Distinct後查詢效率反而提高——SQL優化之Everything is possible

由於原始SQL太長，而且牽扯的表太多，很難說清楚，這裡模擬了一個例子，這個例子由於資料量和SQL的複雜程度限制，無法看出二者執行時間上的明顯差別。這裡從兩種情況的邏輯讀對比來說明問題。

首先建立模擬環境：

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS
  2  WHERE WNER = 'SYS'
  3  AND OBJECT_TYPE NOT LIKE '%BODY'
  4  AND OBJECT_TYPE NOT LIKE 'JAVA%';

Table created.

SQL> CREATE TABLE T2 AS SELECT * FROM DBA_SEGMENTS WHERE WNER = 'SYS';

Table created.

SQL> CREATE TABLE T3 AS SELECT * FROM DBA_INDEXES WHERE WNER = 'SYS';

Table created.

SQL> ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY (OBJECT_NAME);

Table altered.

SQL> CREATE INDEX IND_T2_SEGNAME ON T2(SEGMENT_NAME);

Index created.

SQL> CREATE INDEX IND_T3_TABNAME ON T3(TABLE_NAME);

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100', CASCADE => TRUE)

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100', CASCADE => TRUE)

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T3', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100', CASCADE => TRUE)

PL/SQL procedure successfully completed.

仍然沿用上面兩篇文章例子中的結構，看看原始SQL和增加DISTINCT後的差別：

SQL> SET AUTOT TRACE  
SQL> SELECT T1.OBJECT_NAME, T1.OBJECT_TYPE, T2.TABLESPACE_NAME
  2  FROM T1, T2
  3  WHERE T1.OBJECT_NAME = T2.SEGMENT_NAME
  4  AND T1.OBJECT_NAME IN 
  5     (
  6             SELECT INDEX_NAME FROM T3
  7             WHERE T3.TABLESPACE_NAME = T2.TABLESPACE_NAME
  8     );

311 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=12 Card=668 Bytes=62124)
   1    0   HASH JOIN (SEMI) (Cost=12 Card=668 Bytes=62124)
   2    1     HASH JOIN (Cost=9 Card=668 Bytes=39412)
   3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=668 Bytes=21376)
   4    2       TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=3806 Bytes=102762)
   5    1     TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=340 Bytes=11560)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         93  consistent gets
          0  physical reads
          0  redo size
       8843  bytes sent via SQL*Net to client
        723  bytes received via SQL*Net from client
         22  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        311  rows processed

SQL> SELECT DISTINCT T1.OBJECT_NAME, T1.OBJECT_TYPE, T2.TABLESPACE_NAME
  2  FROM T1, T2
  3  WHERE T1.OBJECT_NAME = T2.SEGMENT_NAME
  4  AND T1.OBJECT_NAME IN 
  5     (
  6             SELECT INDEX_NAME FROM T3
  7             WHERE T3.TABLESPACE_NAME = T2.TABLESPACE_NAME
  8     );

311 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=16 Card=1 Bytes=93)
   1    0   SORT (UNIQUE) (Cost=16 Card=1 Bytes=93)
   2    1     HASH JOIN (Cost=12 Card=1 Bytes=93)
   3    2       HASH JOIN (Cost=5 Card=668 Bytes=44088)
   4    3         TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=340 Bytes=11560)
   5    3         TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=668 Bytes=21376)
   6    2       TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=3806 Bytes=102762)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         72  consistent gets
          0  physical reads
          0  redo size
       8843  bytes sent via SQL*Net to client
        723  bytes received via SQL*Net from client
         22  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        311  rows processed

從SQL執行的統計資訊可以看出，新增DISTINCT後，語句的邏輯讀反而比不加DISTINCT要低。為什麼會產生這種情況，這還要從執行計劃說起。

不加DISTINCT的情況，由於使用IN子查詢的查詢，Oracle對第二個連線採用了HASH JOIN SEMI，這種HASH JOIN SEMI相對於普通的HASH JOIN，代價要大一些。

而新增了DISTINCT之後，Oracle知道最終肯定要進行排序去重的操作，因此在連線的時候就選擇了HASH JOIN作為了連線方式。這就是為什麼加上了DISTINCT之後，邏輯讀反而減少了。但是同時，加上了DISTINCT之後，語句增加了一個排序操作，而在不加DISTINCT的時候，是沒有這個操作的。

當連線的表資料量很大，但是SELECT的最終結果不是很多，且SELECT列的個數不是很多的時候，加上DISTINCT之後，這個排序的代價要小於SEMI JOIN連線的代價。這就是增加一個DISTINCT操作查詢效率反而提高，這個似乎不可能發生的情況的真正原因。

最後需要說明一下，這篇文章意在說明，優化的時候沒有什麼東西是一成不變的，幾乎任何事情都有可能發生，不要被一些所謂死規則限制住。明白了這一點就可以了。這篇文章並不是打算提供一種優化SQL的方法，嚴格意義上將，加上DISTINCT和不加DISTINCT是兩個完全不同的SQL語句。雖然在這個例子中，二者是等價的，但是這是表結構、約束條件和資料本身共同限制的結果。換了另一個環境，這兩個SQL得到的結果可能會相去甚遠，所以，不要試圖將本文的例子作為優化時的一種方法。