如何解析Oracle中exists與in的執(zhí)行效率問題，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。
成都創(chuàng)新互聯(lián)公司10多年成都定制網(wǎng)站服務;為您提供網(wǎng)站建設,網(wǎng)站制作,網(wǎng)頁設計及高端網(wǎng)站定制服務,成都定制網(wǎng)站及推廣,對茶樓設計等多個行業(yè)擁有多年的網(wǎng)站設計經(jīng)驗的網(wǎng)站建設公司。
in是把外表和內(nèi)表作hash join，而exists是對外表作loop，每次loop再對內(nèi)表進行查詢。一般大家都認為exists比in語句的效率要高，這種說法其實是不準確的，這個是要區(qū)分環(huán)境的。
exists對外表用loop逐條查詢，每次查詢都會查看exists的條件語句，當 exists里的條件語句能夠返回記錄行時(無論記錄行是的多少，只要能返回)，條件就為真，返回當前l(fā)oop到的這條記錄，反之如果exists里的條件語句不能返回記錄行，則當前l(fā)oop到的這條記錄被丟棄，exists的條件就像一個bool條件，當能返回結果集則為true，不能返回結果集則為 false。
例如：
select * from user where exists (select 1);
對user表的記錄逐條取出，由于子條件中的select 1永遠能返回記錄行，那么user表的所有記錄都將被加入結果集，所以與 select * from user;是一樣的
又如下
select * from user where exists (select * from user where userId = 0);
可以知道對user表進行l(wèi)oop時，檢查條件語句(select * from user where userId = 0),由于userId永遠不為0，所以條件語句永遠返回空集，條件永遠為false，那么user表的所有記錄都將被丟棄
not exists與exists相反，也就是當exists條件有結果集返回時，loop到的記錄將被丟棄，否則將loop到的記錄加入結果集
總的來說，如果A表有n條記錄，那么exists查詢就是將這n條記錄逐條取出，然后判斷n遍exists條件
in查詢相當于多個or條件的疊加，這個比較好理解，比如下面的查詢
select * from user where userId in (1, 2, 3);
等效于
select * from user where userId = 1 or userId = 2 or userId = 3;
not in與in相反，如下
select * from user where userId not in (1, 2, 3);
等效于
select * from user where userId != 1 and userId != 2 and userId != 3;
總的來說，in查詢就是先將子查詢條件的記錄全都查出來，假設結果集為B，共有m條記錄，然后在將子查詢條件的結果集分解成m個，再進行m次查詢
值得一提的是，in查詢的子條件返回結果必須只有一個字段，例如
select * from user where userId in (select id from B);
而不能是
select * from user where userId in (select id, age from B);
而exists就沒有這個限制
下面來考慮exists和in的性能：
對于以上兩種情況，in是在內(nèi)存里遍歷比較，而exists需要查詢數(shù)據(jù)庫，所以當B表數(shù)據(jù)量較大時，exists效率優(yōu)于in。
考慮如下SQL語句
select * from A where exists (select * from B where B.id = A.id);
select * from A where A.id in (select id from B);
1、select * from A where exists (select * from B where B.id = A.id);
exists()會執(zhí)行A.length次，它并不緩存exists()結果集，因為exists()結果集的內(nèi)容并不重要，重要的是其內(nèi)查詢語句的結果集空或者非空，空則返回false，非空則返回true。
它的查詢過程類似于以下過程：
for ($i = 0; $i < count(A); $i++) {
$a = get_record(A, $i); #從A表逐條獲取記錄
if (B.id = $a[id]) #如果子條件成立
$result[] = $a;
}
return $result;
當B表比A表數(shù)據(jù)大時適合使用exists()，因為它沒有那么多遍歷操作，只需要再執(zhí)行一次查詢就行。
如：A表有10000條記錄，B表有1000000條記錄，那么exists()會執(zhí)行10000次去判斷A表中的id是否與B表中的id相等。
如：A表有10000條記錄，B表有100000000條記錄，那么exists()還是執(zhí)行10000次，因為它只執(zhí)行A.length次，可見B表數(shù)據(jù)越多，越適合exists()發(fā)揮效果。
再如：A表有10000條記錄，B表有100條記錄，那么exists()還是執(zhí)行10000次，還不如使用in()遍歷10000*100次，因為in()是在內(nèi)存里遍歷比較，而exists()需要查詢數(shù)據(jù)庫，我們都知道查詢數(shù)據(jù)庫所消耗的性能更高，而內(nèi)存比較很快。
結論：exists()適合B表比A表數(shù)據(jù)大的情況
2、select * from A where id in (select id from B);
in()只執(zhí)行一次，它查出B表中的所有id字段并緩存起來。之后，檢查A表的id是否與B表中的id相等，如果相等則將A表的記錄加入結果集中，直到遍歷完A表的所有記錄。
它的查詢過程類似于以下過程：
Array A=(select * from A);
Array B=(select id from B);
for(int i=0;i
for(int j=0;j
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
}
}
}
return resultSet;
可以看出，當B表數(shù)據(jù)較大時不適合使用in()，因為它會B表數(shù)據(jù)全部遍歷一次
如：A表有10000條記錄，B表有1000000條記錄，那么最多有可能遍歷10000*1000000次，效率很差。
再如：A表有10000條記錄，B表有100條記錄，那么最多有可能遍歷10000*100次，遍歷次數(shù)大大減少，效率大大提升。
結論：in()適合B表比A表數(shù)據(jù)小的情況
當A表數(shù)據(jù)與B表數(shù)據(jù)一樣大時，in與exists效率差不多，可任選一個使用。
在插入記錄前，需要檢查這條記錄是否已經(jīng)存在，只有當記錄不存在時才執(zhí)行插入操作，可以通過使用 EXISTS 條件句防止插入重復記錄。
insert into A (name,age) select name,age from B where not exists (select 1 from
A where A.id=B.id);
EXISTS與IN的使用效率的問題，通常情況下采用exists要比in效率高，因為IN不走索引。但要看實際情況具體使用：IN適合于外表大而內(nèi)表小的情況；EXISTS適合于外表小而內(nèi)表大的情況。
下面再看not exists和 not in
1、select * from A where not exists (select * from B where B.id = A.id);
2、select * from A where A.id not in (select id from B);
看查詢1，還是和上面一樣，用了B的索引；而對于查詢2，可以轉(zhuǎn)化成如下語句
select * from A where A.id != 1 and A.id != 2 and A.id != 3;
可以知道not in是個范圍查詢，這種!=的范圍查詢無法使用任何索引,等于說A表的每條記錄，都要在B表里遍歷一次，查看B表里是否存在這條記錄
not in 和not exists：如果查詢語句使用了not in 那么內(nèi)外表都進行全表掃描，沒有用到索引；而not extsts 的子查詢依然能用到表上的索引。所以無論那個表大，用not exists都比not in要快，故not exists比not in效率高。
in 與 =的區(qū)別
select name from student where name in ('zhang','wang','li','zhao');
與
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
的結果是相同的。
在我們一般的觀點中，總是認為使用EXISTS(或NOT EXISTS)通常將提高查詢的效率，所以一般推薦使用exists來代替in。但實際情況是不是這個樣子呢？我們分別在兩種不同的優(yōu)化器模式下用實際的例子來看一下：
SEIANG@seiang11g>create table wjq1 as select * from dba_objects;
Table created.
SEIANG@seiang11g>create table wjq2 as select * from dba_tables ;
Table created.
SEIANG@seiang11g>create index idx_object_name on wjq1(object_name);
Index created.
SEIANG@seiang11g>create index idx_table_name on wjq2(table_name);
Index created.
SEIANG@seiang11g>select count(*) from wjq1;
COUNT(*)
----------
86976
SEIANG@seiang11g>select count(*) from wjq2;
COUNT(*)
----------
2868
SEIANG@seiang11g>select * from wjq1 where object_name in (select table_name from wjq2 where table_name like 'M%');
815 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1638414738
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1238 | 270K| 354 (1)| 00:00:05 |
|* 1 | HASH JOIN RIGHT SEMI| | 1238 | 270K| 354 (1)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | IDX_TABLE_NAME | 772 | 13124 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | WJQ1 | 5503 | 1112K| 347 (1)| 00:00:05 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"="TABLE_NAME")
2 - access("TABLE_NAME" LIKE 'M%')
filter("TABLE_NAME" LIKE 'M%')
3 - filter("OBJECT_NAME" LIKE 'M%')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
1462 consistent gets
1256 physical reads
0 redo size
46140 bytes sent via SQL*Net to client
1117 bytes received via SQL*Net from client
56 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
815 rows processed
SEIANG@seiang11g>select * from wjq1 where exists (select 1 from wjq2 where wjq1.object_name=wjq2.table_name and wjq2.table_name like 'M%');
815 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1638414738
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1238 | 270K| 354 (1)| 00:00:05 |
|* 1 | HASH JOIN RIGHT SEMI| | 1238 | 270K| 354 (1)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | IDX_TABLE_NAME | 772 | 13124 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | WJQ1 | 5503 | 1112K| 347 (1)| 00:00:05 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("WJQ1"."OBJECT_NAME"="WJQ2"."TABLE_NAME")
2 - access("WJQ2"."TABLE_NAME" LIKE 'M%')
filter("WJQ2"."TABLE_NAME" LIKE 'M%')
3 - filter("WJQ1"."OBJECT_NAME" LIKE 'M%')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
1462 consistent gets
1242 physical reads
0 redo size
46140 bytes sent via SQL*Net to client
1117 bytes received via SQL*Net from client
56 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
815 rows processed
通過上面執(zhí)行計劃對比發(fā)現(xiàn)：
在CBO模式下，我們可以看到這兩者的執(zhí)行計劃完全相同，統(tǒng)計數(shù)據(jù)也相同。
我們再來看一下RBO模式下的情況，這種情況相對復雜一些。
SEIANG@seiang11g>select /*+ rule*/ * from wjq1 where object_name in (select table_name from wjq2 where table_name like 'M%');
815 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 144941173
--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | VIEW | VW_NSO_1 |
| 4 | SORT UNIQUE | |
|* 5 | INDEX RANGE SCAN | IDX_TABLE_NAME |
|* 6 | INDEX RANGE SCAN | IDX_OBJECT_NAME |
| 7 | TABLE ACCESS BY INDEX ROWID| WJQ1 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TABLE_NAME" LIKE 'M%')
filter("TABLE_NAME" LIKE 'M%')
6 - access("OBJECT_NAME"="TABLE_NAME")
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
698 consistent gets
0 physical reads
0 redo size
55187 bytes sent via SQL*Net to client
1117 bytes received via SQL*Net from client
56 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
815 rows processed
SEIANG@seiang11g>select /*+ rule*/ * from wjq1 where exists (select 1 from wjq2 where wjq1.object_name=wjq2.table_name and wjq2.table_name like 'M%');
815 rows selected.
Elapsed: 00:00:00.15
Execution Plan
----------------------------------------------------------
Plan hash value: 3545670754
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | TABLE ACCESS FULL| WJQ1 |
|* 3 | INDEX RANGE SCAN | IDX_TABLE_NAME |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "WJQ2" "WJQ2" WHERE
"WJQ2"."TABLE_NAME"=:B1 AND "WJQ2"."TABLE_NAME" LIKE 'M%'))
3 - access("WJQ2"."TABLE_NAME"=:B1)
filter("WJQ2"."TABLE_NAME" LIKE 'M%')
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
91002 consistent gets
1242 physical reads
0 redo size
46140 bytes sent via SQL*Net to client
1117 bytes received via SQL*Net from client
56 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
815 rows processed
通過上面兩個執(zhí)行計劃的對比發(fā)現(xiàn)：
在這里，我們可以看到實際上，使用in效率比exists效率更高。我們可以這樣來理解這種情況：
對于in，RBO優(yōu)化器選擇的內(nèi)存查詢的結果作為驅(qū)動表來進行nest loops連接，所以當內(nèi)存查詢的結果集比較小的時候，這個in的效率還是比較高的。
對于exists，RBO優(yōu)化器則是利用外查詢表的全表掃描結果集過濾內(nèi)查詢的結果集，當外查詢的表比較大的時候，相對效率比較低。
SEIANG@seiang11g>select * from wjq2 where table_name in (select object_name from wjq1 where object_name like 'S%');
278 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 1807911610
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 278 | 164K| 55 (0)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 278 | 164K| 55 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| WJQ2 | 278 | 146K| 31 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_OBJECT_NAME | 4435 | 285K| 24 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TABLE_NAME"="OBJECT_NAME")
2 - filter("TABLE_NAME" LIKE 'S%')
3 - access("OBJECT_NAME" LIKE 'S%')
filter("OBJECT_NAME" LIKE 'S%')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
67 recursive calls
0 db block gets
403 consistent gets
446 physical reads
0 redo size
22852 bytes sent via SQL*Net to client
721 bytes received via SQL*Net from client
20 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
278 rows processed
SEIANG@seiang11g>
SEIANG@seiang11g>select * from wjq2 where exists (select 1 from wjq1 where wjq1.object_name=wjq2.table_name and wjq1.object_name like 'S%');
278 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 1807911610
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 278 | 164K| 55 (0)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 278 | 164K| 55 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| WJQ2 | 278 | 146K| 31 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_OBJECT_NAME | 4435 | 285K| 24 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("WJQ1"."OBJECT_NAME"="WJQ2"."TABLE_NAME")
2 - filter("WJQ2"."TABLE_NAME" LIKE 'S%')
3 - access("WJQ1"."OBJECT_NAME" LIKE 'S%')
filter("WJQ1"."OBJECT_NAME" LIKE 'S%')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
295 consistent gets
2 physical reads
0 redo size
22852 bytes sent via SQL*Net to client
721 bytes received via SQL*Net from client
20 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
278 rows processed
通過上面兩個執(zhí)行計劃的對比發(fā)現(xiàn)：
雖然他們的執(zhí)行計劃相同，但是使用exists比使用in的物理讀和邏輯讀明顯小很多，所以使用exists效率更高一下。
看完上述內(nèi)容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝您對創(chuàng)新互聯(lián)的支持。
重慶分公司
重慶分公司
