熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

適時增加Distinct提高查詢效率

2013-11-13 16:24:51  來源: Oracle 
只有增加DISTINCT關鍵字Oracle必然需要對後面的所有字段進行排序以前也經常發現由於開發人員對SQL不是很理解在SELECT列表的多個字段前面添加了DISTINCT造成查詢基本上不可能執行完成甚至產生ORA錯誤所以一直向開發人員強調DISTINCT給性能帶來的影響

  沒想到開發人員在測試一條大的SQL的時候告訴我如果加上了DISTINCT則查詢大概需要分鐘左右可以執行完如果不加DISTINCT則查詢執行了多分鐘仍然得不到結果

  首先想到的是可能DISTINCT是在子查詢中由於加上了DISTINCT將第一步結果集縮小了導致查詢性能提高結果一看SQL發現DISTINCT居然是在查詢的最外層

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

  首先建立模擬環境 




SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS
   WHERE OWNER = SYS
   AND OBJECT_TYPE NOT LIKE %BODY
   AND OBJECT_TYPE NOT LIKE JAVA%;
  Table created
  SQL> CREATE TABLE T AS SELECT * FROM DBA_SEGMENTS WHERE OWNER = SYS;
  Table created
  SQL> CREATE TABLE T AS SELECT * FROM DBA_INDEXES WHERE OWNER = SYS;
  Table created
  SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (OBJECT_NAME);
  Table altered
  SQL> CREATE INDEX IND_T_SEGNAME ON T(SEGMENT_NAME);
  Index created
  SQL> CREATE INDEX IND_T_TABNAME ON T(TABLE_NAME);
  Index created
  SQL> EXEC DBMS_STATSGATHER_TABLE_STATS(USER T METHOD_OPT => FOR ALL INDEXED COLUMNS SIZE CASCADE => TRUE)
  PL/SQL procedure successfully completed
  SQL> EXEC DBMS_STATSGATHER_TABLE_STATS(USER T METHOD_OPT => FOR ALL INDEXED COLUMNS SIZE CASCADE => TRUE)
  PL/SQL procedure successfully completed
  SQL> EXEC DBMS_STATSGATHER_TABLE_STATS(USER T METHOD_OPT => FOR ALL INDEXED COLUMNS SIZE CASCADE => TRUE)
  PL/SQL procedure successfully completed

  仍然沿用上面例子中的結構看看原始SQL和增加DISTINCT後的差別




SQL> SET AUTOT TRACE
  SQL> SELECT TOBJECT_NAME TOBJECT_TYPE TTABLESPACE_NAME
   FROM T T
   WHERE TOBJECT_NAME = TSEGMENT_NAME
   AND TOBJECT_NAME IN
   (
   SELECT INDEX_NAME FROM T
   WHERE TTABLESPACE_NAME = TTABLESPACE_NAME
   );
   rows selected
  Execution Plan
  
   SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
   HASH JOIN (SEMI) (Cost= Card= Bytes=)
   HASH JOIN (Cost= Card= Bytes=)
   TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=)
   TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=)
   TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=)
  Statistics
  
   recursive calls
   db block gets
   consistent gets
   physical reads
   redo size
   bytes sent via SQL*Net to client
   bytes received via SQL*Net from client
   SQL*Net roundtrips to/from client
   sorts (memory)
   sorts (disk)
   rows processed
  SQL> SELECT DISTINCT TOBJECT_NAME TOBJECT_TYPE TTABLESPACE_NAME
   FROM T T
   WHERE TOBJECT_NAME = TSEGMENT_NAME
   AND TOBJECT_NAME IN
   (
   SELECT INDEX_NAME FROM T
   WHERE TTABLESPACE_NAME = TTABLESPACE_NAME
   );
   rows selected
  Execution Plan
  
   SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
   SORT (UNIQUE) (Cost= Card= Bytes=)
   HASH JOIN (Cost= Card= Bytes=)
   HASH JOIN (Cost= Card= Bytes=)
   TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=)
   TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=)
   TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=)
  Statistics
  
   recursive calls
   db block gets
   consistent gets
   physical reads
   redo size
   bytes sent via SQL*Net to client
   bytes received via SQL*Net from client
   SQL*Net roundtrips to/from client
   sorts (memory)
   sorts (disk)
   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得到的結果可能會相去甚遠所以不要試圖將本文的例子作為優化時的一種方法


From:http://tw.wingwit.com/Article/program/Oracle/201311/18286.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.