oracle中關於in和exists，not in 和 not exists

scu2005發表於2009-12-09
Oracle
in和exists
    in 是把外表和內表作hash 連線，而exists是對外表作loop迴圈，每次loop迴圈再對內表進行查詢。
not exists:做NL，對子查詢先查，有個虛表，有確定值，所以就運算元查詢有NULL最終也有值返回
not in:做hash，對子查詢表建立記憶體陣列，用外表匹配，那子查詢要是有NULL那外表沒的匹配最終無值返回。
    一直以來認為exists比in效率高的說法是不準確的。
 
如果查詢的兩個表大小相當，那麼用in和exists差別不大。

如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in：

例如：表A（小表），表B（大表）
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列的索引。
 
not in 和not exists
如果查詢語句使用了not in 那麼內外表都進行全表掃描，沒有用到索引；
而not extsts 的子查詢依然能用到表上的索引。
所以無論那個表大，用not exists都比not in要快。
一直聽到的都是說盡量用exists不要用in，因為exists只判斷存在而in需要對比值，所以exists比較快，但看了看網上的一些東西才發現根本不是這麼回事。
下面這段是抄的
Select * from T1 where x in ( select y from T2 )
執行的過程相當於:
select *
  from t1, ( select distinct y from t2 ) t2
 where t1.x = t2.y;

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

從我的角度來說，in的方式比較直觀，exists則有些繞，而且in可以用於各種子查詢，而exists好像只用於關聯子查詢（其他子查詢當然也可以用，可惜沒意義）。
由於exists是用loop的方式，所以，迴圈的次數對於exists影響最大，所以，外表要記錄數少，內表就無所謂了，而in用的是hash join，所以內表如果小，整個查詢的範圍都會很小，如果內表很大，外表如果也很大就很慢了，這時候exists才真正的會快過in的方式。
 
not in 和not exists
如果查詢語句使用了not in 那麼內外表都進行全表掃描，沒有用到索引；
而not extsts 的子查詢依然能用到表上的索引。
所以無論那個表大，用not exists都比not in要快。

 也就是說，in和exists需要具體情況具體分析，not in和not exists就不用分析了，儘量用not exists就好了。

 

典型的連線型別共有3種：
排序 - - 合併連線(Sort Merge Join (SMJ) )
巢狀迴圈(Nested Loops (NL) )
雜湊連線(Hash Join)

巢狀迴圈和雜湊連線的演算法還是有不同，在理論上雜湊連線要快過排序和nl，當然實際情況比理論上有複雜的多，不過兩者還是有差異的.

 

1 關聯子查詢與非關聯子查詢

 關聯子查詢需要在內部引用外部表，而非關聯子查詢不要引用外部表。對於父查詢中處理的記錄來說，一個關聯子查詢是每行計算一次，然而一個非關聯子查詢只會執行一次，而且結果集被儲存在記憶體中（如果結果集比較小），或者放在一張oracle臨時資料段中（如果結果集比較大）。一個“標量”子查詢是一個非關聯子查詢，返回唯一記錄。如果子查詢僅僅返回一個記錄，那麼oracle優化器會將結果縮減為一個常量，而且這個子查詢只會執行一次。

/*select * from emp where deptno in (select deptno from dept where dept_name='admin');*/

2.如何選擇？

  根據外部查詢，以及子查詢本身所返回的記錄的數目。如果兩種查詢返回的結果是相同的，哪一個效率更好？

  關聯子查詢的系統開銷：對於返回到外層查詢的記錄來說，子查詢會每次執行一次。因此，必須保證任何可能的時候子查詢都要使用索引。

  非關聯子查詢的系統開銷：子查詢只會執行一次，而且結果集通常是排好序的，並儲存在臨時資料段中，其中每一個記錄在返回時都會被父級查詢引用，在子查詢返回大量記錄的情況下，將這些結果集排序回增大系統的開銷。

  所以：如果父查詢只返回較少的記錄，那麼再次執行子查詢的開銷不會非常大，如果返回很多資料行，那麼直查詢就會執行很多次。 如果子查詢返回較少的記錄，那麼為記憶體中儲存父查詢的結果集的系統開銷不會非常大，如果子查詢返回多行，那麼需要將結果放在臨時段上，然後對資料段排序，以便為負查詢中的每個記錄服務。

 

3結論：1）在使用一個關聯子查詢是，使用in 或者 exists子句的子查詢執行計劃通常都相同

       2）exists子句通常不適於子查詢

       3）在外部查詢返回相對較少記錄時，關聯子查詢比非關聯子查詢執行得要更快。

       4）如果子查詢中只有少量的記錄，則非關聯子查詢會比關聯子查詢執行得更快。

4 子查詢轉化：子查詢可以轉化為標準連線操作

       1）使用in的非關聯子查詢（子查詢唯一）

          條件：1）在整個層次結構中最底層資料表上定義唯一主鍵的資料列存在於子查詢的select列表中

                2）至少有個定義了唯一主鍵的資料列在select列表中，而且定義唯一主鍵的其他資料列都必須有指定的相等標準，不管是直接指定，還是間接指定。

       2）使用exists子句的關聯子查詢

          條件：對於相關條件來說，該子查詢只能返回一個記錄。

 

5。not in和not exists調整

  1）not in 非關聯子查詢：轉化為in寫法下的minus子句

  2）not exists關聯子查詢：這種型別的反連線操作會為外部查詢中每一個記錄進行內部查詢，除了不滿足子查詢中where條件的內部資料表以外，他會過濾掉所有記錄。

    可以重寫：在一個等值連線中指定外部連結條件，然後新增select distinct

    eg:select distinct ... from a,b where a.col1 = b.col1(+) and b.col1 is null

6。在子查詢中使用all any

 

 

1.      1.  簡介

本文簡要介紹了關聯子查詢、非關聯子查詢、IN & EXISTS 、 NOT IN & NOT EXISTS之間的區別；同時對不同資料庫版本下CBO對IN & EXISTS & NOT IN & NOT EXISTS的處理做了一定的闡述。

2.         os、資料庫版本以及測試資料

os：windows 2000 server sp4

db：oracle 10.1.0.2

 

 

set time on

set timing on

 

 

drop table outer_large_t

/

create table outer_large_t

(id number,

c1 varchar2(100),

c2 varchar2(100)

)

/

 

 

create index idx_outer_large_t on outer_large_t(id)

/

 

 

drop table outer_small_t

/

create table outer_small_t

as select *from outer_large_t

where 1=2

/

create index idx_outer_small_t_id on outer_small_t(id)

/

 

 

drop table inner_large_t

/

create table inner_large_t

(id number,

c3 varchar2(100),

c4 varchar2(100)

)

/

create index idx_inner_large_t_1 on inner_large_t(id,c3)

/

drop table inner_small_t

/

create table inner_small_t

(id number,

c3 varchar2(100),

c4 varchar2(100)

)

/

create index idx_inner_small_t on inner_small_t(id,c3)

/

 

 

3.      2.關聯子查詢和非關聯子查詢

測試資料:

truncate table outer_large_t

/

truncate table inner_large_t

/

 

declare

begin

  for i in 1..50000 loop

    insert into outer_large_t values (i,'test','test');   

  end loop;

  for i in 30000..100000 loop

    insert into inner_large_t values (i,'test','test');   

  end loop;

  commit;

end;

/

 

analyze table outer_large_t compute statistics for table for all indexes

/

analyze table inner_large_t compute statistics for table for all indexes

/

 

非關聯子查詢形如：

select count(*) from outer_large_t

where id not in

(select id from inner_large_t)

/

子查詢與父查詢沒有關聯。

關聯子查詢形如：

select count(*) from outer_large_t outer_t

where not exists

(select id from inner_large_t where id = outer_t.id)

/

子查詢與父查詢存在關聯id = outer_t.id。

非關聯子查詢對於exists和not exists是沒有意義的。

看如下實驗：

11:17:00 test@GZSERVER> select count(*) from outer_large_t

11:17:02   2  where id not in

11:17:02   3  (select id from inner_large_t)

11:17:02   4  /

 

 

  COUNT(*)

----------

     29999

 

 

已用時間:  00: 00: 00.04

11:17:02 test@GZSERVER> select count(*) from outer_large_t

11:17:02   2  where id in

11:17:02   3  (select id from inner_large_t)

11:17:02   4  /

 

 

  COUNT(*)

----------

     20001

 

 

已用時間:  00: 00: 00.01

11:17:02 test@GZSERVER> select count(*) from outer_large_t outer_t

11:17:02   2  where not exists

11:17:02   3  (select id from inner_large_t)

11:17:02   4  /

 

 

  COUNT(*)

----------

         0

  

已用時間:  00: 00: 00.00

11:17:02 test@GZSERVER> select count(*) from outer_large_t outer_t

11:17:02   2  where exists

11:17:02   3  (select id from inner_large_t)

11:17:02   4  /

 

  COUNT(*)

----------

     50000

 

 

 

已用時間:  00: 00: 00.00

11:17:03 test@GZSERVER>

非關聯子查詢使用not exists的話父查詢總是返回0，使用exists總是返回父查詢的查詢結果集。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11536986/viewspace-622019/，如需轉載，請註明出處，否則將追究法律責任。

相關文章