怎么增加Distinct提高查詢(xún)效率
這篇文章將為大家詳細(xì)講解有關(guān)怎么增加Distinct提高查詢(xún)效率，小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。
創(chuàng)新互聯(lián)專(zhuān)注于企業(yè)營(yíng)銷(xiāo)型網(wǎng)站建設(shè)、網(wǎng)站重做改版、岑溪網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、H5高端網(wǎng)站建設(shè)、商城系統(tǒng)網(wǎng)站開(kāi)發(fā)、集團(tuán)公司官網(wǎng)建設(shè)、外貿(mào)網(wǎng)站制作、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁(yè)設(shè)計(jì)等建站業(yè)務(wù)，價(jià)格優(yōu)惠性?xún)r(jià)比高，為岑溪等各大城市提供網(wǎng)站開(kāi)發(fā)制作服務(wù)。
只有增加DISTINCT關(guān)鍵字，Oracle必然需要對(duì)后面的所有字段進(jìn)行排序。以前也經(jīng)常發(fā)現(xiàn)由于開(kāi)發(fā)人員對(duì)SQL不是很理解，在 SELECT列表的20多個(gè)字段前面添加了DISTINCT，造成查詢(xún)基本上不可能執(zhí)行完成，甚至產(chǎn)生ORA-7445錯(cuò)誤。所以一直向開(kāi)發(fā)人員強(qiáng)調(diào) DISTINCT給性能帶來(lái)的影響。下面創(chuàng)新互聯(lián)小編來(lái)講解下如何增加Distinct提高查詢(xún)效率?
如何增加Distinct提高查詢(xún)效率
首先想到的是可能DISTINCT是在子查詢(xún)中，由于加上了DISTINCT，將第一步結(jié)果集縮小了，導(dǎo)致查詢(xún)性能提高，結(jié)果一看SQL，發(fā)現(xiàn)DISTINCT居然是在查詢(xún)的最外層。
由于原始SQL太長(zhǎng)，而且牽扯的表太多，很難說(shuō)清楚，這里模擬了一個(gè)例子，這個(gè)例子由于數(shù)據(jù)量和SQL的復(fù)雜程度限制，無(wú)法看出二者執(zhí)行時(shí)間上的明顯差別。這里從兩種情況的邏輯讀對(duì)比來(lái)說(shuō)明問(wèn)題。
首先建立模擬環(huán)境：
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.
仍然沿用上面例子中的結(jié)構(gòu)，看看原始SQL和增加DISTINCT后的差別：
如何增加Distinct提高查詢(xún)效率
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
從統(tǒng)計(jì)信息可以看出，添加了DISTINCT后，語(yǔ)句的邏輯讀反而比不加DISTINCT要高。為什么會(huì)產(chǎn)生這種情況，還要從執(zhí)行計(jì)劃說(shuō)起。
不加DISTINCT的情況，由于使用IN子查詢(xún)的查詢(xún)，Oracle對(duì)第二個(gè)連接采用了HASH JOIN SEMI，這種HASH JOIN SEMI相對(duì)于普通的HASH JOIN，代價(jià)要大一些。
而添加了DISTINCT之后，Oracle知道最終肯定要進(jìn)行排序去重的操作，因此在連接的時(shí)候就選擇了HASH JOIN作為了連接方式。這就是為什么加上了DISTINCT之后，邏輯讀反而減少了。但是同時(shí)，加上了DISTINCT之后，語(yǔ)句增加了一個(gè)排序操作， 而在不加DISTINCT的時(shí)候，是沒(méi)有這個(gè)操作的。
當(dāng)連接的表數(shù)據(jù)量很大，但是SELECT的最終結(jié)果不是很多，且SELECT列的 個(gè)數(shù)不是很多的時(shí)候，加上DISTINCT之后，這個(gè)排序的代價(jià)要小于SEMI JOIN連接的代價(jià)。這就是增加一個(gè)DISTINCT操作查詢(xún)效率反而提高，這個(gè)似乎不可能發(fā)生的情況的真正原因。
最后需要說(shuō)明一下， 這篇文章意在說(shuō)明，優(yōu)化的時(shí)候沒(méi)有什么東西是一成不變的，幾乎任何事情都有可能發(fā)生，不要被一些所謂死規(guī)則限制住。明白了這一點(diǎn)就可以了。這篇文章并不是 打算提供一種優(yōu)化SQL的方法，嚴(yán)格意義上將，加上DISTINCT和不加DISTINCT是兩個(gè)完全不同的SQL語(yǔ)句。雖然在這個(gè)例子中，二者是等價(jià) 的，但是這是表結(jié)構(gòu)、約束條件和數(shù)據(jù)本身共同限制的結(jié)果。換了另一個(gè)環(huán)境，這兩個(gè)SQL得到的結(jié)果可能會(huì)相去甚遠(yuǎn)，所以，不要試圖將本文的例子作為優(yōu)化時(shí) 的一種方法。
關(guān)于“怎么增加Distinct提高查詢(xún)效率”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。
當(dāng)前文章：怎么增加Distinct提高查詢(xún)效率
文章轉(zhuǎn)載：http://m.jiaotiyi.com/article/ihipij.html