只有增加DISTINCT關鍵字，Oracle必然需要對後面的所有字段進行排序。以前也經常發現由於開發人員對SQL不是很理解，在SELECT列表的20多個字段前面添加了DISTINCT，造成查詢基本上不可能執行完成，甚至產生ORA-7445錯誤。所以一直向開發人員強調DISTINCT給性能帶來的影響。
沒想到開發人員在測試一條大的SQL的時候，告訴我如果加上了DISTINCT，則查詢大概需要4分鐘左右可以執行完，如果不加DISTINCT，則查詢執行了10多分鐘，仍然得不到結果。
首先想到的是可能DISTINCT是在子查詢中，由於加上了DISTINCT，將第一步結果集縮小了，導致查詢性能提高，結果一看SQL，發現DISTINCT居然是在查詢的最外層。
由於原始SQL太長，而且牽扯的表太多，很難說清楚，這裡模擬了一個例子，這個例子由於數據量和SQL的復雜程度限制，無法看出二者執行時間上的明顯差別。這裡從兩種情況的邏輯讀對比來說明問題。
首先建立模擬環境：
SQL> CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS
2 WHERE OWNER = '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 OWNER = 'SYS';
Table created.
SQL> CREATE TABLE T3 AS SELECT * FROM DBA_INDEXES WHERE OWNER = '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 Optimizer=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 Optimizer=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
從統計信息可以看出，添加了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得到的結果可能會相去甚遠，所以，不要試圖將本文的例子作為優化時的一種方法。
