1.EXISTS的執(zhí)行流程
select * from t1 where exists ( select null from t2 where y = x )
可以理解為:
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop
對(duì)于in 和 exists的性能區(qū)別:
如果子查詢得出的結(jié)果集記錄較少，主查詢中的表較大且又有索引時(shí)應(yīng)該用in,反之如果外層的主查詢記錄較少，子查詢中的表大，又有索引時(shí)使用exists。
其實(shí)我們區(qū)分in和exists主要是造成了驅(qū)動(dòng)順序的改變（這是性能變化的關(guān)鍵），如果是exists，那么以外層表為驅(qū)動(dòng)表，先被訪問(wèn)，如果是IN，那么先執(zhí)行子查詢，所以我們會(huì)以驅(qū)動(dòng)表的快速返回為目標(biāo)，那么就會(huì)考慮到索引及結(jié)果集的關(guān)系了
另外IN時(shí)不對(duì)NULL進(jìn)行處理
如：
select 1 from dual where null in (0,1,2,null)
為空
2.NOT IN 與NOT EXISTS:
NOT EXISTS的執(zhí)行流程
select .....
from rollup R
where not exists ( select 'Found' from title T
where R.source_id = T.Title_ID);
可以理解為:
for x in ( select * from rollup )
loop
if ( not exists ( that query ) ) then
OUTPUT
end if;
end;
注意:NOT EXISTS 與 NOT IN 不能完全互相替換，看具體的需求。如果選擇的列可以為空，則不能被替換。
例如下面語(yǔ)句，看他們的區(qū)別：
select x,y from t;
x y
------ ------
1 3
3 1
1 2
1 1
3 1
5
select * from t where x not in (select y from t t2 )
no rows
select * from t where not exists (select null from t t2
where t2.y=t.x )
x y
------ ------
5 NULL
所以要具體需求來(lái)決定
對(duì)于not in 和 not exists的性能區(qū)別：
not in 只有當(dāng)子查詢中，select 關(guān)鍵字后的字段有not null約束或者有這種暗示時(shí)用not in,另外如果主查詢中表大，子查詢中的表小但是記錄多，則應(yīng)當(dāng)使用not in,并使用anti hash join.
如果主查詢表中記錄少，子查詢表中記錄多，并有索引，可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外連接+is null
NOT IN 在基于成本的應(yīng)用中較好
比如:
select .....
from rollup R
where not exists ( select 'Found' from title T
where R.source_id = T.Title_ID);
改成（佳）
select ......
from title T, rollup R
where R.source_id = T.Title_id(+)
and T.Title_id is null;
或者（佳）
sql> select /*+ HASH_AJ */ ...
from rollup R
where ource_id NOT IN ( select ource_id
from title T
where ource_id IS NOT NULL )
討論IN和EXISTS。
select * from t1 where x in ( select y from t2 )
事實(shí)上可以理解為：
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
——如果你有一定的SQL優(yōu)化經(jīng)驗(yàn)，從這句很自然的可以想到t2絕對(duì)不能是個(gè)大表，因?yàn)樾枰獙?duì)t2進(jìn)行全表的“唯一排序”，如果t2很大這個(gè)排序的性能是 不可忍受的。但是t1可以很大，為什么呢？最通俗的理解就是因?yàn)閠1.x=t2.y可以走索引。但這并不是一個(gè)很好的解釋。試想，如果t1.x和t2.y 都有索引，我們知道索引是種有序的結(jié)構(gòu)，因此t1和t2之間最佳的方案是走merge join。另外，如果t2.y上有索引，對(duì)t2的排序性能也有很大提高。
select * from t1 where exists ( select null from t2 where y = x )
可以理解為：
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD!
end if
end loop
——這個(gè)更容易理解，t1永遠(yuǎn)是個(gè)表掃描！因此t1絕對(duì)不能是個(gè)大表，而t2可以很大，因?yàn)閥=x.x可以走t2.y的索引。
綜合以上對(duì)IN/EXISTS的討論，我們可以得出一個(gè)基本通用的結(jié)論：IN適合于外表大而內(nèi)表小的情況；EXISTS適合于外表小而內(nèi)表大的情況。
我們要根據(jù)實(shí)際的情況做相應(yīng)的優(yōu)化，不能絕對(duì)的說(shuō)誰(shuí)的效率高誰(shuí)的效率低，所有的事都是相對(duì)的
本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)
點(diǎn)擊舉報(bào)。
