程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> 適時增加Distinct提高查詢效率

適時增加Distinct提高查詢效率

編輯:Oracle數據庫基礎
只有增加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得到的結果可能會相去甚遠,所以,不要試圖將本文的例子作為優化時的一種方法。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved