oracle中關(guān)于in和exists,notin和notexists

1、in 和 exists in是把外表和內(nèi)表作hash連接，而exists是對外表作loop循環(huán)，每次loop 循環(huán)再對內(nèi)表進行查詢。 not exists:做NL,對子查詢先查，有個虛表，有確定值，所以就算子查詢有NULL 最終也有值返回 not in:做hash，對子查詢表建立內(nèi)存數(shù)組，用外表匹配，那子查詢要是有NULL 那外表沒的匹配最終無值返回。 一直以來認為exists比in效率高的說法是不準確的。 如果查詢的兩個表大小相當，那么用in和exists差別不大。 如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的 用in: 例如：表A (小表)，表B (大表)

2、1 : select * from A where cc in (select cc from B) 效率低，用到了 A表上cc列的索引； select * from A where exists(select cc from B where cc=A.cc) 效率高，用到了 B表上cc列的索引。 相反的 2: select * from B where cc in (select cc from A) 效率高，用到了 B表上cc列的索引； select * from B where exists(select cc from A where cc=B.cc) 效率低，用到了 A表上cc列的索

3、引。 not in 和 not exists 如果查詢語句使用了 not in那么內(nèi)外表都進行全表掃描，沒有用到索引； 而not extsts的子查詢依然能用到表上的索引。 所以無論那個表大，用not exists都比not in要快。 一直聽到的都是說盡量用exists不要用in，因為exists只判斷存在而in需要對 比值，所以exists比較快，但看了看網(wǎng)上的一些東西才發(fā)現(xiàn)根本不是這么回事。 下面這段是抄的 Select * from T1 where x in ( select y from T2 ) 執(zhí)行的過程相當于： select * from t1, ( select dist

4、inct y from t2 ) t2 where tl.x = t2.y; select * from t1 where exists ( select null from t2 where y = x ) 執(zhí)行的過程相當于： for x in ( select * from t1 ) loop if ( exists ( select n ull from t2 where y = x.x ) the n OUTPUT THE RECORD end if end loop 從我的角度來說，in的方式比較直觀，exists則有些繞，而且in可以用于各種 子查詢，而exists好像只用于關(guān)聯(lián)子

5、查詢(其他子查詢當然也可以用，可惜沒意 義)。 由于exists是用loop的方式，所以，循環(huán)的次數(shù)對于 exists影響最大，所以， 外表要記錄數(shù)少，內(nèi)表就無所謂了，而in用的是hash join，所以內(nèi)表如果小， 整個查詢的范圍都會很小，如果內(nèi)表很大，外表如果也很大就很慢了，這時候 exists才真正的會快過in的方式。 not in 和 not exists 如果查詢語句使用了 not in那么內(nèi)外表都進行全表掃描，沒有用到索引； 而not extsts的子查詢依然能用到表上的索引。 所以無論那個表大，用not exists都比not in要快。 也就是說，in和exists需要具體情況

6、具體分析，not in和not exists就不用分析 了，盡量用not exists就好了。 典型的連接類型共有3種： 排序-合并連接(Sort Merge Join (SMJ) 嵌套循環(huán)(Nested Loops (NL) 哈希連接(Hash Join) 嵌套循環(huán)和哈希連接的算法還是有不同，在理論上哈希連接要快過排序和 nl，當 然實際情況比理論上有復雜的多，不過兩者還是有差異的 1關(guān)聯(lián)子查詢與非關(guān)聯(lián)子查詢 關(guān)聯(lián)子查詢需要在內(nèi)部引用外部表，而非關(guān)聯(lián)子查詢不要引用外部表。對于父 查詢中處理的記錄來說，一個關(guān)聯(lián)子查詢是每行計算一次，然而一個非關(guān)聯(lián)子查 詢只會執(zhí)行一次，而且結(jié)果集被保存在內(nèi)存中(

7、如果結(jié)果集比較小)，或者放在 一張oracle_臨時數(shù)據(jù)段中(如果結(jié)果集比較大)。一個標量”子查詢是一個非 關(guān)聯(lián)子查詢，返回唯一記錄。如果子查詢僅僅返回一個記錄，那么oracle優(yōu)化 器會將結(jié)果縮減為一個常量，而且這個子查詢只會執(zhí)行一次。 /*select * from emp where dept no in (select dept no from dept where dept_ name=admi n);*/ 2.如何選擇？ 根據(jù)外部查詢，以及子查詢本身所返回的記錄的數(shù)目。如果兩種查詢返回的結(jié) 果是相同的，哪一個效率更好？ 關(guān)聯(lián)子查詢的系統(tǒng)開銷：對于返回到外層查詢的記錄來說，子查詢會每

8、次執(zhí)行 一次。因此，必須保證任何可能的時候子查詢都要使用索引。 非關(guān)聯(lián)子查詢的系統(tǒng)開銷：子查詢只會執(zhí)行一次，而且結(jié)果集通常是排好序的， 并保存在臨時數(shù)據(jù)段中，其中每一個記錄在返回時都會被父級查詢引用，在子查 詢返回大量記錄的情況下，將這些結(jié)果集排序回增大系統(tǒng)的開銷。 所以：如果父查詢只返回較少的記錄，那么再次執(zhí)行子查詢的開銷不會非常大, 如果返回很多數(shù)據(jù)行，那么直查詢就會執(zhí)行很多次。 如果子查詢返回較少的記 錄，那么為內(nèi)存中保存父查詢的結(jié)果集的系統(tǒng)開銷不會非常大， 如果子查詢返回 多行，那么需要將結(jié)果放在臨時段上，然后對數(shù)據(jù)段排序，以便為負查詢中的每 個記錄服務(wù) 3結(jié)論：1）在使用一個關(guān)聯(lián)子查

9、詢是，使用in或者exists子句的子查詢執(zhí)行 計劃通常都相同 2）exists子句通常不適于子查詢 3）在外部查詢返回相對較少記錄時，關(guān)聯(lián)子查詢比非關(guān)聯(lián)子查詢執(zhí)行得要 更快。 4 ）如果子查詢中只有少量的記錄，則非關(guān)聯(lián)子查詢會比關(guān)聯(lián)子查詢執(zhí)行得 更快。 4子查詢轉(zhuǎn)化：子查詢可以轉(zhuǎn)化為標準連接操作 1）使用in的非關(guān)聯(lián)子查詢（子查詢唯一） 條件：1）在整個層次結(jié)構(gòu)中最底層數(shù)據(jù)表上定義唯一主鍵的數(shù)據(jù)列存在 于子查詢的select列表中 2）至少有個定義了唯一主鍵的數(shù)據(jù)列在 select列表中，而且定義唯 一主鍵的其他數(shù)據(jù)列都必須有指定的相等標準，不管是直接指定，還是間接指定， 2）使用exist

10、s子句的關(guān)聯(lián)子查詢 條件：對于相關(guān)條件來說，該子查詢只能返回一個記錄。 5。notin 和 not exists 調(diào)整 1）not in非關(guān)聯(lián)子查詢：轉(zhuǎn)化為in寫法下的min us子句 2）not exists關(guān)聯(lián)子查詢：這種類型的反連接操作會為外部查詢中每一個記錄 進行內(nèi)部查詢，除了不滿足子查詢中 where條件的內(nèi)部數(shù)據(jù)表以外，他會過濾 掉所有記錄。 可以重寫：在一個等值連接中指定外部鏈接條件，然后添加select dist inct eg:select disti net . from a,b where a.coll = b.col1(+) and b.coll is n ull 6。

11、在子查詢中使用all any 1. 1.簡介 本文簡要介紹了關(guān)聯(lián)子查詢、非關(guān)聯(lián)子查詢、 IN end loop; for i in 30000.100000 loop in sert into inn er_large_t values (i,test,test); end loop; commit; end; / in dexes in dexes analyze table outer_large_tcompute statisticsfor table for all / analyze table inner_large_tcompute statisticsfor table for

12、 all / 非關(guān)聯(lián)子查詢形如： select coun t(*) from outer_large_t where id not in (select id from inn er_large_t) / 子查詢與父查詢沒有關(guān)聯(lián)。 關(guān)聯(lián)子查詢形如： select coun t(*) from outer_large_t outer_t where not exists (select id from inn er_large_t where id = outer_t.id) / 子查詢與父查詢存在關(guān)聯(lián)id = outer_t.id 。 非關(guān)聯(lián)子查詢對于exists和not exists是沒有意

13、義的。 看如下實驗： 11:17:00 testGZSERVER select co un t(*) from outer_large_t 11:17:02 2 where id not in 11:17:02 3 (select id from inn er_large_t) 11:17:02 4 / COUNT(*) 29999 已用時間：00: 00: 00.04 11:17:02 testGZSERVER select co un t(*) from outer_large_t 11:17:022 where id in 11:17:023 (select id from inn er_large_t) 11:17:02 COUNT(*) 20001 已用時間：00: 00: 00.01 11:17:02 testGZSERVER select cou nt(*) from outer_large_t outer_t 11:17:022where n ot exists 11:17:023(selectid from inn er_large_t) 11:17:024/ COUNT(*) 0 已用時間：00: 00: 00.00 11:17:02 testGZSERVER select