ACCESS【转载】[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秒,差了3个数据级!为什么吗?其实答案非常简答,以上两只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叫做内表。

假使稍微做综合,就可以抱更详尽的下结论:

1、对于not
exists查询,内表存在空值对查询结果莫影响;对于not
in查询,内表存在空值将致最后的询问结果吧空。

2、对于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);

 

       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之间的于也该结束了。

相关文章