突破常識：SQL增加DISTINCT後查詢效率反而提高

abstractcyj發表於2016-04-13
SQL

轉一篇楊廷琨(yangtingkun)老師的好文：

只要增加了DISTINCT關鍵字，Oracle就會對隨後跟著的所有欄位進行排序去重。以前也經常發現由於開發人員對SQL不是很理解，在SELECT列表的20多個欄位前面新增了DISTINCT，造成查詢的執行異常緩慢，基本上很難在ORA-1555錯誤出現之前得到查詢的結果，甚至有些SQL會產生ORA-7445錯誤。所以在給開發人員培訓的時候還著重介紹了一下DISTINCT的功能以及不正確地使用DISTINCT所帶來的效能方面的負面影響。


不過這次碰到了一個有趣的現象：開發人員在測試一個比較複雜的SQL時發現如果SQL中加上了DISTINCT，則查詢大概要花費4分鐘左右；而如果不加DISTINCT，則查詢執行了10多分鐘仍然沒有返回結果。


根據這樣的描述，首先想到的是可能DISTINCT是在查詢的最內層，由於加上DISTINCT使得第一步的結果集縮小了，從而導致查詢效能的提高。但一看SQL才發現，DISTINCT居然是在查詢的最外層。


由於原始SQL很複雜，牽扯太多的表，很難表述清楚。因此這裡模擬了一個例子，這個例子由於受到資料量和SQL複雜程度的限制，所以是否新增DISTINCT對SQL執行時間沒有太大的影響，但是兩個SQL邏輯讀的差異還是可以說明一定問題的。


首先建立模擬環境：

SQL> CREATE TABLE T1 AS SELECT * FROMDBA_OBJECTS

2   WHERE OWNER = 'SYS'

3   AND OBJECT_TYPE NOT LIKE '%BODY' AND OBJECT_TYPENOT LIKE 'JAVA%';

Table created.

SQL> CREATE TABLE T2 AS SELECT * FROMDBA_SEGMENTS WHERE OWNER = 'SYS';

Table created.


SQL> CREATE TABLE T3 AS SELECT * FROMDBA_INDEXES WHERE OWNER = 'SYS';

Table created.

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

Table altered.

SQL> CREATE INDEX IND_T2_SEGNAME ONT2(SEGMENT_NAME);

Index created.

SQL> CREATE INDEX IND_T3_TABNAME ONT3(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 WHERE T1.OBJECT_NAME = T2.SEGMENT_NAMEAND T1.OBJECT_NAME IN

3    (SELECT INDEX_NAME FROM T3 WHERE T3.TABLESPACE_NAME= T2.TABLESPACE_NAME);

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     HASHJOIN (Cost=9 Card=668 Bytes=39412)

 3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=668Bytes=21376)

 4    2       TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=3806Bytes=102762)

 5    1     TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=340Bytes=11560)

Statistics

----------------------------------------------------------

         93         consistentgets
           0       sorts (memory)
           0       sorts (disk)
        311         rowsprocessed

SQL> SELECT DISTINCT T1.OBJECT_NAME,T1.OBJECT_TYPE, T2.TABLESPACE_NAME

2  FROM T1, T2 WHERE T1.OBJECT_NAME= T2.SEGMENT_NAME AND T1.OBJECT_NAME IN

3     ( SELECT INDEX_NAME FROM T3 WHERET3.TABLESPACE_NAME = T2.TABLESPACE_NAME);

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=340Bytes=11560)

 5    3         TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=668Bytes=21376)

 6    2       TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=3806Bytes=102762)

Statistics


----------------------------------------------------------

         72 consistent gets
           1       sorts (memory)
        311 rows processed


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


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


如果新增了DISTINCT：CBO清楚知道在最後一步肯定要進行排序去重的操作，因此在連線時就選擇了HASH JOIN作為連線方式。這就是加上了DISTINCT後，邏輯讀反而減少的原因。不過加上DISTINCT後，執行計劃增加了一個排序操作；而在不加DISTINCT時是沒有這個操作的。


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


最後要說明一點，舉這個例子意在說明：優化時沒有什麼東西是一成不變的，幾乎任何事情都有可能發生，不要被一些所謂規則限制住。


這篇文章並不是在介紹一種優化SQL的方法，嚴格意義上講，加上DISTINCT和不加DISTINCT是兩個完全不同的SQL語句。雖然在這個例子中二者是等價的，但這是表結構、約束條件和資料本身共同限制的結果，換成另一個環境，這兩個SQL得到的結果可能會相去甚遠。因此這兩個SQL實際上並不等價，不要試圖將本文的例子作為優化時的一種方法。

轉自：http://mp.weixin.qq.com/s?__biz=MjM5MDAxOTk2MQ==&mid=2650270186&idx=1&sn=61b1efbf1a096f3ea39d560ef0141f51&scene=0#wechat_redirect

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

相關文章