当前位置:Gxlcms > mysql > 子查询in、exists、notin、notexists一点补充

子查询in、exists、notin、notexists一点补充

时间:2021-07-01 10:21:17 帮助过:16人阅读

子查询的一点补充,之前小鱼写过一篇关于in和exists性能的分析 http://www.dbaxiaoyu.com/archives/2012 其实这个都是子查询,而在最新的oracle 11g中,in和exists基本不太可能产生变化,因为11g的cbo不仅可以unnest展开子查询为表连接,还新增了null-aware

子查询的一点补充,之前小鱼写过一篇关于in和exists性能的分析 http://www.dbaxiaoyu.com/archives/2012

其实这个都是子查询,而在最新的oracle 11g中,in和exists基本不太可能产生变化,因为11g的cbo不仅可以unnest展开子查询为表连接,还新增了null-aware anti join的算法,由于in对null敏感。

而在oracle 11g之前,如果关联列上面没有not null的约束,那么此时not in的写法就无法对子查询进行展开,一般我们会看见形如下面的filter执行计划:
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue May 13 10:14:42 2014

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set autotrace traceonly;
SQL> set linesize 140;
SQL> select * from table02 where object_id not in (select object_id from table01);

Execution Plan
----------------------------------------------------------
Plan hash value: 206984988

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52376 | 9053K| 3430 (1)| 00:00:42 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TABLE02 | 52408 | 9058K| 154 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| TABLE01 | 50979 | 647K| 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TABLE01" "TABLE01"
WHERE LNNVL("OBJECT_ID"
:B1)))
3 - filter(LNNVL("OBJECT_ID"
:B1))

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
17188464 consistent gets
0 physical reads
0 redo size
1403 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

而这个执行成本往往非常高,而如果我们添加一个not null的约束,或者改写下sql或者添加not null约束来取消这个特别消耗成本的filter

1)改写成minus写法:
SQL> select * from table02 a minus
2 select * from table02 where object_id in (select object_id from table01);

Execution Plan
----------------------------------------------------------
Plan hash value: 1546480765

--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 52408 | 18M| | 4674 (54)| 00
:00:57 |
| 1 | MINUS | | | | | |
|
| 2 | SORT UNIQUE | | 52408 | 9058K| 21M| 2189 (1)| 00
:00:27 |
| 3 | TABLE ACCESS FULL | TABLE02 | 52408 | 9058K| | 154 (2)| 00
:00:02 |
| 4 | SORT UNIQUE | | 52409 | 9724K| 19M| 2484 (1)| 00
:00:30 |
|* 5 | HASH JOIN | | 52409 | 9724K| | 308 (2)| 00
:00:04 |
| 6 | TABLE ACCESS FULL| TABLE01 | 53662 | 681K| | 153 (1)| 00
:00:02 |
| 7 | TABLE ACCESS FULL| TABLE02 | 52408 | 9058K| | 154 (2)| 00
:00:02 |
--------------------------------------------------------------------------------
--------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID"="OBJECT_ID")

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
2296 consistent gets
0 physical reads
0 redo size
1403 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed

这里逻辑读降了好多,虽然cost感觉好像比上述的filter执行成本还要大,但是sql的相应时间确明显比filter好太多了。

2 给子表和主表增加not null的约束:

SQL> alter table table01 modify object_id not null;
Table altered.
SQL> alter table table02 modify object_id not null;
Table altered.

SQL> select * from table02 where object_id not in (select object_id from table01);

Execution Plan
----------------------------------------------------------
Plan hash value: 35610947

--------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 190 | 308 (2)| 00:00:04 |

|* 1 | HASH JOIN RIGHT ANTI| | 1 | 190 | 308 (2)| 00:00:04 |

| 2 | TABLE ACCESS FULL | TABLE01 | 53662 | 681K| 153 (1)| 00:00:02 |

| 3 | TABLE ACCESS FULL | TABLE02 | 52408 | 9058K| 154 (2)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID"="OBJECT_ID")

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
265 recursive calls
0 db block gets
1557 consistent gets
0 physical reads
0 redo size
1403 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

注意这里需要对子表和主表都添加not null约束,不然在10g的cbo下,oracle还是会选择性能较差的filter。

我们看看各个版本优化器对于in和exists处理的变化(Table01和table02的object_id上都有not null约束)
SQL> select /*+ optimizer_features_enable('8.1.7')*/* from table02 b where exists (select 1 from table01 a where a.object_id=b.object_id);

50075 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 206984988

--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2806 | 485K| 67 |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL| TABLE02 | 2806 | 485K| 67 |
|* 3 | TABLE ACCESS FULL| TABLE01 | 561 | 7293 | 67 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT 0 FROM "TABLE01" "A" WHERE
"A"."OBJECT_ID"=:B1))
3 - filter("A"."OBJECT_ID"=:B1)

Note
-----
- cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17191469 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50075 rows processed

SQL> select /*+ optimizer_features_enable('8.1.7')*/* from table02 b where not
exists (select 1 from table01 a where a.object_id=b.object_id);

Execution Plan
----------------------------------------------------------
Plan hash value: 206984988

--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2806 | 485K| 67 |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL| TABLE02 | 2806 | 485K| 67 |
|* 3 | TABLE ACCESS FULL| TABLE01 | 561 | 7293 | 67 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( NOT EXISTS (SELECT 0 FROM "TABLE01" "A" WHERE
"A"."OBJECT_ID"=:B1))
3 - filter("A"."OBJECT_ID"=:B1)

Note
-----
- cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17191469 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select /*+ optimizer_features_enable('8.1.7')*/* from table02 b where objec
t_id in (select object_id from table01 a);

50075 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2067593584

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31M| 5705M| | 469 |
|* 1 | HASH JOIN | | 31M| 5705M| | 469 |
| 2 | VIEW | VW_NSO_1 | 56115 | 712K| | 251 |
| 3 | SORT UNIQUE | | 56115 | 712K| 2216K| 251 |
| 4 | TABLE ACCESS FULL| TABLE01 | 56115 | 712K| | 67 |
| 5 | TABLE ACCESS FULL | TABLE02 | 56115 | 9699K| | 67 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID"="$nso_col_1")

Note
-----
- cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4684 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50075 rows processed

SQL> select /*+ optimizer_features_enable('8.1.7')*/* from table02 b where objec
t_id not in (select object_id from table01 a);

Execution Plan
----------------------------------------------------------
Plan hash value: 206984988

--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2806 | 485K| 67 |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL| TABLE02 | 2806 | 485K| 67 |
|* 3 | TABLE ACCESS FULL| TABLE01 | 561 | 7293 | 67 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( NOT EXISTS (SELECT 0 FROM "TABLE01" "A" WHERE
"OBJECT_ID"=:B1))
3 - filter("OBJECT_ID"=:B1)

Note
-----
- cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4684 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

这里看出在8i的优化器模式下,in的子查询被展开为表连接了,其余的not in、exists、not exists的子查询并不被选择展开为表连接,而是采用一种filter的关联方式,虽然这里的执行成本初看来filter的cost更小,但是sq的相应时间消耗资源的比例确实天壤之别,很多情况我们并不能以cost值去衡量这个sql性能。

SQL> select /*+ optimizer_features_enable('9.2.0')*/* from table02 b where exis
ts (select 1 from table01 a where a.object_id=b.object_id);

50075 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 268410134

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50075 | 5183K| | 236 |
|* 1 | HASH JOIN SEMI | | 50075 | 5183K| 5136K| 236 |
| 2 | TABLE ACCESS FULL | TABLE02 | 50076 | 4547K| | 68 |
| 3 | VIEW | VW_SQ_1 | 50075 | 635K| | 68 |
| 4 | TABLE ACCESS FULL| TABLE01 | 50075 | 244K| | 68 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID"="B"."OBJECT_ID")

Note
-----
- cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4684 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50075 rows processed

SQL> select /*+ optimizer_features_enable('9.2.0')*/* from table02 b where not e
xists (select 1 from table01 a where a.object_id=b.object_id);

Execution Plan
----------------------------------------------------------
Plan hash value: 2991049530

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5629 | 1044K| | 324 |
|* 1 | HASH JOIN ANTI | | 5629 | 1044K| 10M| 324 |
| 2 | TABLE ACCESS FULL| TABLE02 | 58373 | 9M| | 68 |
| 3 | TABLE ACCESS FULL| TABLE01 | 52744 | 669K| | 68 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

Note
-----
- cpu costing is off (consider enabling it)
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4684 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select /*+ optimizer_features_enable('9.2.0')*/* from table02 b where objec
t_id in (select object_id from table01 a);

Execution Plan
----------------------------------------------------------
Plan hash value: 1361234999

------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50075 | 5183K| | 236 |
|* 1 | HASH JOIN SEMI | | 50075 | 5183K| 5136K| 236 |
| 2 | TABLE ACCESS FULL | TABLE02 | 50076 | 4547K| | 68 |
| 3 | VIEW | VW_NSO_1 | 50075 | 635K| | 68 |
| 4 | TABLE ACCESS FULL| TABLE01 | 50075 | 244K| | 68 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID"="$nso_col_1")

Note
-----
- cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4684 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50075 rows processed

SQL> select /*+ optimizer_features_enable('9.2.0')*/* from table02 b where objec
t_id not in (select object_id from table01 a);

Execution Plan
----------------------------------------------------------
Plan hash value: 2991049530

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5629 | 1044K| | 324 |
|* 1 | HASH JOIN ANTI | | 5629 | 1044K| 10M| 324 |
| 2 | TABLE ACCESS FULL| TABLE02 | 58373 | 9M| | 68 |
| 3 | TABLE ACCESS FULL| TABLE01 | 52744 | 669K| | 68 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID"="OBJECT_ID")

Note
-----
- cpu costing is off (consider enabling it)
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4684 consistent gets
0 physical reads
0 redo size
2569714 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

看出在9i优化器环境下,in和exists都被展开为表连接,此时cbo走的hash join的连接方式。
由于主表和子表的object_id上有not null的约束,所以这里not in和not exists执行计划也都相同,对于关联数据较多的sql,hash join往往比nested loop高效很多。

oracle 10g的优化器对于in、exists、not in和not exists区别并不大,到了11g的优化器,新增了null aware anti join算法,此时并不需要表中有not null约束,也能走hash join的连接方式。

关于in、exists、not in和not exists一直是很多朋友纠结的问题,小鱼这里简单总结下:
在oracle 8I下,in是可以展开为表连接的,而not in、exists、not exists会选择filter执行计划,如果被驱动表没有高效索引,驱动表数据返回较多,这个执行计划往往存在很严重的性能问题
在oracle 9I到oracle 10g下,in和exists没有多大性能的区别,而not in和not exists则可能有所区别,主要看关联列是否有not null约束,如果没有也只能走filter的执行计划,而有则会选择hash join和filter的中优秀的执行方式
在oracle 11g下,由于新增了null-aware anti join的算法,in和exists基本没有区别了,既可以走hash join也可以走filter。

从此in、exists、not in、not exists的经典问题可能并不绝对了,虽然优化器有诸多的缺陷,但是cbo确实在不断的改进自己,这个是值得庆幸的!

而现在我们来看看返回结果上有什么区别:
SQL> select * from t01;

ID NAME
---------- ----------
1 xiaoyu
2 xiaobai
3

SQL> select * from t02;

ID NAME
---------- ----------
10 xiaoyu
20 xiaotian

SQL> select * from t01 where t01.name in (select name from t02);

ID NAME
---------- ----------
1 xiaoyu

SQL> select * from t01 where exists (select 1 from t02 where t01.name=t02.name);

ID NAME
---------- ----------
1 xiaoyu

来看看not in和not exists:
SQL> select * from t01 where t01.name not in (select name from t02);

ID NAME
---------- ----------
2 xiaobai

SQL> select * from t01 where not exists (select 1 from t02 where t01.name=t02.na
me);

ID NAME
---------- ----------
3
2 xiaobai

看出这里的子查询中in和exists返回结果没有区别,not in的只返回一行数据,而not exists确返回了两行数据,其实我们应该是希望返回两行数据的,那么如果我们再t02表上面添加一个name null的rows来看看

SQL> insert into t02 values(30,null);
1 row created.
SQL> commit;
Commit complete.

SQL> select * from t01 where name in (select name from t02);

ID NAME
---------- ----------
1 xiaoyu
SQL> select * from t01 where exists (select 1 from t02 where t01.name=t02.name);

ID NAME
---------- ----------
1 xiaoyu

SQL> select * from t01 where name not in (select name from t02);

no rows selected

SQL> select * from t01 where not exists (select 1 from t02 where t01.name=t02.na
me);

ID NAME
---------- ----------
3
2 xiaobai

这里看出in和exists对于null处理没有变化,但是not in和not exists就不同了,not exists对于子表的null会直接略掉,也就是认为满足这个not exists的条件,而not in对于子表的null是敏感的,换句话说只要子表有null值,则not in不返回任何结果集。

关于in和exists补充就到此为止了,话说最近手头正有个子查询不展开的案例,该走hash join的走的是filter,整理完后会与大家分享!

人气教程排行