如何解析Oracle中exists與in的執(zhí)行效率問(wèn)題

如何解析Oracle中exists與in的執(zhí)行效率問(wèn)題，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。

成都創(chuàng)新互聯(lián)公司10多年成都定制網(wǎng)站服務(wù);為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁(yè)設(shè)計(jì)及高端網(wǎng)站定制服務(wù),成都定制網(wǎng)站及推廣,對(duì)茶樓設(shè)計(jì)等多個(gè)行業(yè)擁有多年的網(wǎng)站設(shè)計(jì)經(jīng)驗(yàn)的網(wǎng)站建設(shè)公司。

in是把外表和內(nèi)表作hash join，而exists是對(duì)外表作loop，每次loop再對(duì)內(nèi)表進(jìn)行查詢。一般大家都認(rèn)為exists比in語(yǔ)句的效率要高，這種說(shuō)法其實(shí)是不準(zhǔn)確的，這個(gè)是要區(qū)分環(huán)境的。

exists對(duì)外表用loop逐條查詢，每次查詢都會(huì)查看exists的條件語(yǔ)句，當(dāng) exists里的條件語(yǔ)句能夠返回記錄行時(shí)(無(wú)論記錄行是的多少，只要能返回)，條件就為真，返回當(dāng)前l(fā)oop到的這條記錄，反之如果exists里的條件語(yǔ)句不能返回記錄行，則當(dāng)前l(fā)oop到的這條記錄被丟棄，exists的條件就像一個(gè)bool條件，當(dāng)能返回結(jié)果集則為true，不能返回結(jié)果集則為 false。

例如：

select * from user where exists (select 1);

對(duì)user表的記錄逐條取出，由于子條件中的select 1永遠(yuǎn)能返回記錄行，那么user表的所有記錄都將被加入結(jié)果集，所以與 select * from user;是一樣的

又如下

select * from user where exists (select * from user where userId = 0);

可以知道對(duì)user表進(jìn)行l(wèi)oop時(shí)，檢查條件語(yǔ)句(select * from user where userId = 0),由于userId永遠(yuǎn)不為0，所以條件語(yǔ)句永遠(yuǎn)返回空集，條件永遠(yuǎn)為false，那么user表的所有記錄都將被丟棄

not exists與exists相反，也就是當(dāng)exists條件有結(jié)果集返回時(shí)，loop到的記錄將被丟棄，否則將loop到的記錄加入結(jié)果集

總的來(lái)說(shuō)，如果A表有n條記錄，那么exists查詢就是將這n條記錄逐條取出，然后判斷n遍exists條件 

in查詢相當(dāng)于多個(gè)or條件的疊加，這個(gè)比較好理解，比如下面的查詢

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;

總的來(lái)說(shuō)，in查詢就是先將子查詢條件的記錄全都查出來(lái)，假設(shè)結(jié)果集為B，共有m條記錄，然后在將子查詢條件的結(jié)果集分解成m個(gè)，再進(jìn)行m次查詢

值得一提的是，in查詢的子條件返回結(jié)果必須只有一個(gè)字段，例如

select * from user where userId in (select id from B);

而不能是

select * from user where userId in (select id, age from B);

而exists就沒(méi)有這個(gè)限制

下面來(lái)考慮exists和in的性能：

對(duì)于以上兩種情況，in是在內(nèi)存里遍歷比較，而exists需要查詢數(shù)據(jù)庫(kù)，所以當(dāng)B表數(shù)據(jù)量較大時(shí)，exists效率優(yōu)于in。

考慮如下SQL語(yǔ)句

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()會(huì)執(zhí)行A.length次，它并不緩存exists()結(jié)果集，因?yàn)閑xists()結(jié)果集的內(nèi)容并不重要，重要的是其內(nèi)查詢語(yǔ)句的結(jié)果集空或者非空，空則返回false，非空則返回true。
它的查詢過(guò)程類似于以下過(guò)程：

for ($i = 0; $i < count(A); $i++) {

$a = get_record(A, $i); #從A表逐條獲取記錄

if (B.id = $a[id]) #如果子條件成立

$result[] = $a;

}

return $result;

當(dāng)B表比A表數(shù)據(jù)大時(shí)適合使用exists()，因?yàn)樗鼪](méi)有那么多遍歷操作，只需要再執(zhí)行一次查詢就行。
如：A表有10000條記錄，B表有1000000條記錄，那么exists()會(huì)執(zhí)行10000次去判斷A表中的id是否與B表中的id相等。
如：A表有10000條記錄，B表有100000000條記錄，那么exists()還是執(zhí)行10000次，因?yàn)樗粓?zhí)行A.length次，可見(jiàn)B表數(shù)據(jù)越多，越適合exists()發(fā)揮效果。
再如：A表有10000條記錄，B表有100條記錄，那么exists()還是執(zhí)行10000次，還不如使用in()遍歷10000*100次，因?yàn)閕n()是在內(nèi)存里遍歷比較，而exists()需要查詢數(shù)據(jù)庫(kù)，我們都知道查詢數(shù)據(jù)庫(kù)所消耗的性能更高，而內(nèi)存比較很快。

結(jié)論：exists()適合B表比A表數(shù)據(jù)大的情況

2、select * from A where id in (select id from B);

in()只執(zhí)行一次，它查出B表中的所有id字段并緩存起來(lái)。之后，檢查A表的id是否與B表中的id相等，如果相等則將A表的記錄加入結(jié)果集中，直到遍歷完A表的所有記錄。

它的查詢過(guò)程類似于以下過(guò)程：

Array A=(select * from A);  

Array B=(select id from B);  

for(int i=0;i<a.length;i++) {   </a.length;i++) {  <>

   for(int j=0;j<b.length;j++) {   </b.length;j++) {  <>

      if(A[i].id==B[j].id) {  

         resultSet.add(A[i]);  

         break;  

      }  

   }  

}  

return resultSet;

可以看出，當(dāng)B表數(shù)據(jù)較大時(shí)不適合使用in()，因?yàn)樗鼤?huì)B表數(shù)據(jù)全部遍歷一次
如：A表有10000條記錄，B表有1000000條記錄，那么最多有可能遍歷10000*1000000次，效率很差。
再如：A表有10000條記錄，B表有100條記錄，那么最多有可能遍歷10000*100次，遍歷次數(shù)大大減少，效率大大提升。

結(jié)論：in()適合B表比A表數(shù)據(jù)小的情況

當(dāng)A表數(shù)據(jù)與B表數(shù)據(jù)一樣大時(shí)，in與exists效率差不多，可任選一個(gè)使用。

在插入記錄前，需要檢查這條記錄是否已經(jīng)存在，只有當(dāng)記錄不存在時(shí)才執(zhí)行插入操作，可以通過(guò)使用 EXISTS 條件句防止插入重復(fù)記錄。
insert into A (name,age) select name,age from B where not exists (select 1 from A where A.id=B.id);

    EXISTS與IN的使用效率的問(wèn)題，通常情況下采用exists要比in效率高，因?yàn)镮N不走索引。但要看實(shí)際情況具體使用：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的索引；而對(duì)于查詢2，可以轉(zhuǎn)化成如下語(yǔ)句

select * from A where A.id != 1 and A.id != 2 and A.id != 3;

可以知道not in是個(gè)范圍查詢，這種!=的范圍查詢無(wú)法使用任何索引,等于說(shuō)A表的每條記錄，都要在B表里遍歷一次，查看B表里是否存在這條記錄

not in 和not exists：如果查詢語(yǔ)句使用了not in 那么內(nèi)外表都進(jìn)行全表掃描，沒(méi)有用到索引；而not extsts 的子查詢依然能用到表上的索引。所以無(wú)論那個(gè)表大，用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' 

的結(jié)果是相同的。

在我們一般的觀點(diǎn)中，總是認(rèn)為使用EXISTS(或NOT EXISTS)通常將提高查詢的效率，所以一般推薦使用exists來(lái)代替in。但實(shí)際情況是不是這個(gè)樣子呢？我們分別在兩種不同的優(yōu)化器模式下用實(shí)際的例子來(lái)看一下：

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

一、內(nèi)查詢結(jié)果集比較小，而外查詢較大的時(shí)候的情況

1、在CBO模式下：

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

通過(guò)上面執(zhí)行計(jì)劃對(duì)比發(fā)現(xiàn)：
   在CBO模式下，我們可以看到這兩者的執(zhí)行計(jì)劃完全相同，統(tǒng)計(jì)數(shù)據(jù)也相同。

 

 

我們?cè)賮?lái)看一下RBO模式下的情況，這種情況相對(duì)復(fù)雜一些。

 

 

2、在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

 

 通過(guò)上面兩個(gè)執(zhí)行計(jì)劃的對(duì)比發(fā)現(xiàn)：
   在這里，我們可以看到實(shí)際上，使用in效率比exists效率更高。我們可以這樣來(lái)理解這種情況：
   對(duì)于in，RBO優(yōu)化器選擇的內(nèi)存查詢的結(jié)果作為驅(qū)動(dòng)表來(lái)進(jìn)行nest loops連接，所以當(dāng)內(nèi)存查詢的結(jié)果集比較小的時(shí)候，這個(gè)in的效率還是比較高的。
   對(duì)于exists，RBO優(yōu)化器則是利用外查詢表的全表掃描結(jié)果集過(guò)濾內(nèi)查詢的結(jié)果集，當(dāng)外查詢的表比較大的時(shí)候，相對(duì)效率比較低。

 

 

二、內(nèi)查詢結(jié)果集比較大，而外查詢較小的時(shí)候的情況

1、在CBO模式下：

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

通過(guò)上面兩個(gè)執(zhí)行計(jì)劃的對(duì)比發(fā)現(xiàn)：
   雖然他們的執(zhí)行計(jì)劃相同，但是使用exists比使用in的物理讀和邏輯讀明顯小很多，所以使用exists效率更高一下。

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝您對(duì)創(chuàng)新互聯(lián)的支持。

網(wǎng)站標(biāo)題：如何解析Oracle中exists與in的執(zhí)行效率問(wèn)題
網(wǎng)站地址：http://muchs.cn/article2/pdgjic.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站導(dǎo)航、網(wǎng)站設(shè)計(jì)公司、網(wǎng)站排名、小程序開(kāi)發(fā)、外貿(mào)網(wǎng)站建設(shè)、

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)