【转发】[ORACLE]详解not in与not exists的不同与用法

在网上搜了下有关oracle中not
exists和not
in质量的可比,发现没有描述的太完善的,或许是问题太不难了,达人们都不屑于解释吗。于是本人花了点时间,试图把那一个难题归纳描述清楚,其实说到底一句话:not in品质并不比not
exists差,关键看你用的是否科学。

 

自作者先建八个示范表,便于表达:

create table  ljn_test1 (col number);

create table  ljn_test2 (col number);

然后插入一些数量:

insert into ljn_test1

select level from dual connect by level
<=30000;

insert into ljn_test2

select level+1 from dual connect by level
<=30000;

commit;

然后来分别看一下使用not exists和not in的属性差别:

select * from ljn_test1 where not exists (select 1
from ljn_test2 where ljn_test1.col = ljn_test2.col);

 

       COL


         1

 

Elapsed: 00:00:00.06

select * from ljn_test1 where col not in (select
col from ljn_test2);

 

       COL


         1

 

Elapsed: 00:00:21.28

可以见到,使用not exists必要0.06秒,而采用not in须要21秒,差了一个数据级!为啥吗?其实答案很简答,以上多少个SQL其实并不是等价的。

本身把以上三个表的数目清除掉,重新插入数据:

truncate table ljn_test1;

truncate table ljn_test2;

insert into ljn_test1 values(1);

insert into ljn_test1 values(2);

insert into ljn_test1 values(3);

insert into ljn_test2 values(2);

insert into ljn_test2 values(null);

commit;

接下来重新实施多个SQL:

select * from ljn_test1 where not exists (select 1
from ljn_test2 where ljn_test1.col = ljn_test2.col);

 

       COL


         3

         1

 

select * from ljn_test1 where col not in (select
col from ljn_test2);

 

no rows selected

这回not
in的实质暴光了,竟然拿到的是空集。来精心分解一下原因:

A.  select *
from ljn_test1 where col not in (select col from
ljn_test2);

A在这些事例中可以转化为上边的B:

B.  select *
from ljn_test1 where col not in (2,null);

B可以更进一步转化为上边的C:

C.  select *
from ljn_test1 where col <> 2 and col <>
null;

因为col <>
null是多少个永假式,所以末了意识到的结果自然相当于空了。

通过可以得出结论:只要not
in的子查询中包蕴空值,那么最终的结果就为空!

not exists语句不会产出这种情景,因为not exists子句中写的是ljn_test1与ljn_test2的涉嫌,null是不参预等值关联的,所以ljn_test2的col存在空值对终极的查询结果尚未其余影响。

本人在此地权且把ljn_test1叫做外表,ljn_test2叫做内表。

若果稍做综合,就足以博得更详实的下结论:

壹,对于not
exists查询,内表存在空值对查询结果尚未影响;对于not
in查询,内表存在空值将导致最终的询问结果为空。

二,对于not
exists查询,外表存在空值,存在空值的那条记下最后会输出;对于not
in查询,外表存在空值,存在空值的那条记下最终将被过滤,其余数据不受影响。

 

讲到那里,作者就足以起来解释为什么下面的not
in语句比not exists语句功用差这么多了。

not
exists语句很惹人注目就是贰个回顾的两表关联,内表与外表中留存空值自身就不参与关联,在CBO(基于开销的优化器)中常用的履行布置是hash
join,所以它的功用完全没有毛病,看一下它的施行布署:

set autot on;

select * from ljn_test1 where not exists (select 1
from ljn_test2 where ljn_test1.col = ljn_test2.col);

 

       COL


         3

         1

 

Elapsed: 00:00:00.01

 

Execution Plan


Plan hash value: 385135874

 


| Id  |
Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |


|   0 | SELECT
STATEMENT   |           |     3 |    78 |     7  (15)| 00:00:01
|

|*  1 |  HASH JOIN ANTI    |           |     3 |    78 |     7  (15)| 00:00:01
|

|   2 |   TABLE ACCESS FULL| LJN_TEST1 |     3 |    39 |     3   (0)| 00:00:01
|

|   3 |   TABLE ACCESS FULL| LJN_TEST2 |     2 |    26 |     3   (0)| 00:00:01
|


 

Predicate Information (identified by operation
id):


 

   1 –
access(“LJN_TEST1″.”COL”=”LJN_TEST2″.”COL”)

 

其一执行布署很清晰,没有怎么须要表明的,再看一下not
in:

 

select * from ljn_test1 where col not in (select
col from ljn_test2);

 

no rows selected

 

Elapsed: 00:00:00.01

 

Execution Plan


Plan hash value: 3267714838

 


| Id  |
Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |


|   0 | SELECT
STATEMENT   |           |     1 |    13 |     5   (0)| 00:00:01
|

|*  1 |  FILTER            |           |       |       |           |          |

|   2 |   TABLE ACCESS FULL| LJN_TEST1 |     3 |    39 |     3   (0)| 00:00:01
|

|*  3 |   TABLE ACCESS FULL| LJN_TEST2 |     2 |    26 |     2   (0)| 00:00:01
|


 

Predicate Information (identified by operation
id):


 

   1 – filter(
NOT EXISTS (SELECT 0 FROM “LJN_TEST2” “LJN_TEST2”

              WHERE
LNNVL(“COL”<>:B1)))

   3 –
filter(LNNVL(“COL”<>:B1))

 

可以看出关联谓词是filter,它就像于两表关联中的nested
loop,也等于跑两层循环,可知它的频率有多差。为啥not
in不大概动用hash
join作为实践安排吧?正如上边表达的,因为内表或外部中留存空值对最后结出发生的熏陶是hash join无法落实的,因为hash
join不帮衬把空值放到hash桶中,所以它不或者处理外表和内表中留存的空值,功效与科学放在一块儿时,肯定是要采用正确,所以oracle必须丢弃成效,保障科学,选择filter谓词。

 

以此执行布署中我们还有感兴趣的东西,那就是:LNNVL(“COL”<>:B1),关于LNNVL的解释能够参见官方文档:http://download.oracle.com/docs/cd/B19306\_01/server.102/b14200/functions078.htm

它在此处的作用很巧妙,oracle知道使用filter质量很差,所以它在扫描内表ljn_test2时,会使用LNNVL来检查ljn_test2.col是还是不是留存null值,只要扫描到null值,就可以判断最终的结果为空值,也就不曾了继续执行的含义,所以oracle可以登时为止执行,在某种意义上它弥补了filter较差的属性。

自家用例子来证实那或多或少,首先先造一些数目:

truncate table ljn_test1;

truncate table ljn_test2;

insert into ljn_test1

select level from dual connect by level
<=30000;

insert into ljn_test2

select level+1 from dual connect by level
<=30000;

commit;

下一场作者为着让oracle尽快扫描到ljn_test2.col为null的那条记下,小编要先找到物理地址最小的那条记下,因为普通状态全表扫描会先扫描物理地址最小的那条记下:

select col from ljn_test2 where rowid=(select
min(rowid) from ljn_test2);

 

       COL


      1982

下一场本身把那条记下更新为空:

update ljn_test2 set col = null where
col=1982;

commit;

下一场再来看一下not in的询问成效:

select * from ljn_test1 where col not in (select
col from ljn_test2);

 

no rows selected

 

Elapsed: 00:00:00.17

 

寓目那一个结果后作者很爽,它和前面查询需要用时21秒有很大的反差!

本来,我们不或然连续指望oracle扫描表时总是第一找到null值,看下边的事例:

update ljn_test2 set col = 1982 where col is
null;

select col from ljn_test2 where rowid=(select
max(rowid) from ljn_test2);

 

       COL


     30001

update ljn_test2 set col = null where
col=30001;

commit;

再看一下not in的询问功效:

select * from ljn_test1 where col not in (select
col from ljn_test2);

 

       COL


         1

 

Elapsed: 00:00:21.11

这一弹指间not in再两回原形毕露了!

机会主义不行,更杯具的是假诺内表中从未空值,那LNNVL优化就永远起不到成效,相反它还会附加费用!

实在只要找到原因,难点很好消除,不就是空值在肇事嘛!在健康的逻辑下用户本来就是想博得和not exists等价的查询结果,所以假设让oracle知道大家不必要空值参预进去就足以了。

首先种缓解方案:

将内表与外表的涉及字段设定为非空的

alter table ljn_test1 modify col not null;

alter table ljn_test2 modify col not null;

好了,再看一下实施布置:

set autot on;

select * from ljn_test1 where col not in (select
col from ljn_test2);

 

       COL


         1

 

Elapsed: 00:00:00.07

 

Execution Plan


Plan hash value: 385135874

 


| Id  |
Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |


|   0 | SELECT
STATEMENT   |           |     1 |    26 |    28   (8)| 00:00:01
|

|*  1 |  HASH JOIN ANTI    |           |     1 |    26 |    28   (8)| 00:00:01
|

|   2 |   TABLE ACCESS FULL| LJN_TEST1 | 30000
|   380K|    13   (0)| 00:00:01
|

|   3 |   TABLE ACCESS FULL| LJN_TEST2 | 30000
|   380K|    13   (0)| 00:00:01
|


 

Predicate Information (identified by operation
id):


 

   1 –
access(“COL”=”COL”)

 

很好!那回oracle已经知道使用hash
join了!不过有时候表中需求仓储空值,那时候就无法在表结构上指定非空了,那也同样简单:

第二种缓解方案:

查询时在内表与外部中过滤空值。

先把表结构復苏为允许空值的:

alter table ljn_test1 modify col null;

alter table ljn_test2 modify col null;

下一场改造查询:

select * from ljn_test1 where col is not null and
col not in (select col from ljn_test2 where col is not null);

 

ACCESS,       COL


         1

 

Elapsed: 00:00:00.07

 

Execution Plan


Plan hash value: 385135874

 


| Id  |
Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |


|   0 | SELECT
STATEMENT   |           |     1 |    26 |    28   (8)| 00:00:01
|

|*  1 |  HASH JOIN ANTI    |           |     1 |    26 |    28   (8)| 00:00:01
|

|*  2 |   TABLE ACCESS FULL| LJN_TEST1 | 30000
|   380K|    13   (0)| 00:00:01
|

|*  3 |   TABLE ACCESS FULL| LJN_TEST2 | 30000
|   380K|    13   (0)| 00:00:01
|


 

Predicate Information (identified by operation
id):


 

   1 –
access(“COL”=”COL”)

   2 –
filter(“COL” IS NOT NULL)

   3 –
filter(“COL” IS NOT NULL)

 

OK! hash join出来了!小编想作者有关not
exists与not
in之间的相比较也该终结了。

相关文章