联系：手机/微信(+86 17813235971) QQ(107644445)
标题：_optimizer_null_aware_antijoin和not in效率
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
准备两个测试表
SQL> conn chf/oracle
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> create table t_xifenfei
2 as select * from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
COUNT(*)
----------
86259
SQL> create table t_xifenfei1
2 as select * from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei1;
COUNT(*)
----------
86260
--删除部分记录，用来做not in的内部表
SQL> delete from t_xifenfei where object_id>86200;
918 rows deleted.
SQL> commit;
Commit complete.
查询_optimizer_null_aware_antijoin隐含参数默认值
SQL> conn / as sysdba
Connected.
SQL> col name for a52
SQL> col value for a24
SQL> col description for a50
set linesize 150
select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
from x$ksppi a,x$ksppcv b
where a.inst_id = USERENV ('Instance')
and b.inst_id = USERENV ('Instance')
and a.indx = b.indx
and upper(a.ksppinm) LIKE upper('%¶m%')
order by name
SQL> SQL> 2 3 4 5 6 7 8 /
Enter value for param: _optimizer_null_aware_antijoin
old 6: and upper(a.ksppinm) LIKE upper('%¶m%')
new 6: and upper(a.ksppinm) LIKE upper('%_optimizer_null_aware_antijoin%')
NAME VALUE DESCRIPTION
---------------------------------------------------- ------------------------ -----------------------------
_optimizer_null_aware_antijoin TRUE null-aware antijoin parameter
_optimizer_null_aware_antijoin从11.1.0.6开始引进,默认为true
_optimizer_null_aware_antijoin为true,执行not in
SQL> conn chf/oracle
Connected.
SQL> set autot trace
SQL> set timing on
SQL> set lines 150
SQL> set pages 1000
SQL> select count(*) from t_xifenfei1 where object_id not in(select object_id from t_xifenfei);
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 4048525918
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 688 (1)| 00:00:09 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | HASH JOIN RIGHT ANTI NA| | 1137 | 11370 | 688 (1)| 00:00:09 |
| 3 | TABLE ACCESS FULL | T_XIFENFEI | 85341 | 416K| 344 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL | T_XIFENFEI1 | 86260 | 421K| 344 (1)| 00:00:05 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"="OBJECT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2472 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
_optimizer_null_aware_antijoin为false,执行not in
SQL> alter session set "_optimizer_null_aware_antijoin"=false;
Session altered.
Elapsed: 00:00:00.00
SQL> select count(*) from t_xifenfei1 where object_id not in(select object_id from t_xifenfei);
Elapsed: 00:02:29.64
Execution Plan
----------------------------------------------------------
Plan hash value: 2503880249
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 25M (1)| 86:20:57 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| T_XIFENFEI1 | 86260 | 421K| 344 (1)| 00:00:05 |
|* 4 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 5 | 344 (1)| 00:00:05 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS (SELECT 0 FROM "T_XIFENFEI" "T_XIFENFEI" WHERE
LNNVL("OBJECT_ID"<>:B1)))
4 - filter(LNNVL("OBJECT_ID"<>:B1))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
52982891 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这里很明显,当 _optimizer_null_aware_antijoin为false的时候not in效率非常低(当in里面记录多，使用FILTER效率肯定低下).
_optimizer_null_aware_antijoin为false,执行not exists
SQL> alter session set "_optimizer_null_aware_antijoin"=false;
Session altered.
SQL>select count(*) from t_xifenfei1 b where not exists
2 (select 1 from t_xifenfei a where a.object_id=b.object_id);
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 2976307246
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 688 (1)| 00:00:09 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | HASH JOIN RIGHT ANTI| | 1137 | 11370 | 688 (1)| 00:00:09 |
| 3 | TABLE ACCESS FULL | T_XIFENFEI | 85341 | 416K| 344 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL | T_XIFENFEI1 | 86260 | 421K| 344 (1)| 00:00:05 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2472 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
对于11g的版本可以通过_optimizer_null_aware_antijoin参数开启NULL-aware Anti join特性来提高not in的效率,对于11g以下版本可以通过not exists来提高效率