只有增加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_STATS
GATHER_TABLE_STATS(USER
T
METHOD_OPT =>
FOR ALL INDEXED COLUMNS SIZE
CASCADE => TRUE)
PL/SQL procedure successfully completed
SQL> EXEC DBMS_STATS
GATHER_TABLE_STATS(USER
T
METHOD_OPT =>
FOR ALL INDEXED COLUMNS SIZE
CASCADE => TRUE)
PL/SQL procedure successfully completed
SQL> EXEC DBMS_STATS
GATHER_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 T
OBJECT_NAME
T
OBJECT_TYPE
T
TABLESPACE_NAME
FROM T
T
WHERE T
OBJECT_NAME = T
SEGMENT_NAME
AND T
OBJECT_NAME IN
(
SELECT INDEX_NAME FROM T
WHERE T
TABLESPACE_NAME = T
TABLESPACE_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 T
OBJECT_NAME
T
OBJECT_TYPE
T
TABLESPACE_NAME
FROM T
T
WHERE T
OBJECT_NAME = T
SEGMENT_NAME
AND T
OBJECT_NAME IN
(
SELECT INDEX_NAME FROM T
WHERE T
TABLESPACE_NAME = T
TABLESPACE_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
