[20230903]执行计划ANTI SNA和ANTI NA表示什么.txt
发布时间 2023-09-04 20:42:51作者: lfree
--//在not in的sql语句什么出现ANTI SNA或者ANTI NA(注：不会出现在not exists语句中)，我自己是非常混乱的。
--//我看了以前的链接 http://blog.itpub.net/267265/viewspace-2157424/ =>[20180705]关于hash join 2.txt
--//还是发现一些自己以前的错误，尝试做一些解析，也许还是不对。
--//首先ANTI 表示反。Aware表示意识清楚的意思。single 表示单边。
--//NA => Null-Aware.
--//SNA => Single Null-Aware
--//官方的解析如下:
The ANTI SNA stands for "single null-aware antijoin." ANTI NA stands for "null-aware antijoin." The null-aware operation
enables the optimizer to use the antijoin optimization even on a nullable column. In releases earlier than Oracle
Database 11g, the database could not perform antijoins on NOT IN queries when nulls were possible.
--//我自己开始的理解既然single表示单边的意思，就是仅仅1边做Null-Aware。这样Null-Aware最开始的理解就是两边，但是我以前的
--//测试把自己带偏了,还是通过例子说明问题。
1.环境:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
create table t1 ( id number,name varchar2(10));
create table t2 ( id number,name varchar2(10));
insert into t1 values (null,'t1null');
insert into t2 values (null,'t2null');
insert into t1 select level id ,'t1'||to_char(level) name from dual connect by level<=4;
insert into t2 select level+1 id ,'t2'||to_char(level+1) name from dual connect by level<=4;
commit ;
--//分析略.
2.测试：
SCOTT@test01p> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@test01p> select * from t1 where id not in (select id from t2 ) ;
no rows selected
Plan hash value: 1275484728
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 0 |00:00:00.01 | 13 | | | |
|* 1 | HASH JOIN ANTI NA | | 1 | 2 | 22 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 13 | 1995K| 1995K| 1053K (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 5 | 40 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| T2 | 1 | 5 | 15 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="ID")
--//出现的是HASH JOIN ANTI NA，所以我当时第1个感觉两个表都做Null-Aware。但是当我执行如下：
SCOTT@test01p> select * from t1 where id not in (select id from t2 ) and t1.id is not null;
no rows selected
Plan hash value: 1275484728
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 0 |00:00:00.01 | 13 | | | |
|* 1 | HASH JOIN ANTI NA | | 1 | 1 | 11 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 13 | 1995K| 1995K| 1053K (0)|
|* 2 | TABLE ACCESS FULL| T1 | 1 | 4 | 32 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| T2 | 1 | 5 | 15 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="ID")
2 - filter("T1"."ID" IS NOT NULL)
--//出现的还是HASH JOIN ANTI NA,我当时的理解以为会出现SNA，仅仅在id=2,加入了过滤条件("T1"."ID" IS NOT NULL)。
--//似乎给我的感觉是NA仅仅作用于t2表。
--//补充：
SCOTT@test01p> select /*+ SWAP_JOIN_INPUTS(@SEL$5DA710D3 , T2@SEL$2) */ * from t1 where id not in (select id from t2 ) and t1.id is not null;
no rows selected
Plan hash value: 2739594415
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 0 |00:00:00.01 | 6 | | | |
|* 1 | HASH JOIN RIGHT ANTI NA| | 1 | 1 | 11 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 6 | 1995K| 1995K| 458K (0)|
| 2 | TABLE ACCESS FULL | T2 | 1 | 5 | 15 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | | | |
|* 3 | TABLE ACCESS FULL | T1 | 0 | 4 | 32 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T2@SEL$2
3 - SEL$5DA710D3 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="ID")
3 - filter("T1"."ID" IS NOT NULL)
--//可以发现如果t2.id 存在null，根本没有执行id=3,t1表的全表扫描操作(starts=0)。
--//而当我执行如下：
SCOTT@test01p> select * from t1 where id not in (select id from t2 where t2.id is not null) ;
ID NAME
---------- --------------------
1 t11
Plan hash value: 1270581391
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 1 |00:00:00.01 | 14 | | | |
|* 1 | HASH JOIN ANTI SNA| | 1 | 2 | 22 | 6 (0)| 00:00:01 | 1 |00:00:00.01 | 14 | 1995K| 1995K| 1054K (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 5 | 40 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 7 | | | |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 4 | 12 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="ID")
3 - filter("T2"."ID" IS NOT NULL)
--//出现的是HASH JOIN ANTI SNA,很明显输出排除了t1.id is null的输出，相当于加入条件t1.id is not null.
--//所以当时我推导出了一个结论，对于前面的例子sna作用于表t1，na作用于表T2(而且是反了的，不好表述)，注当时连接使用左右连接表
--//述有点不合适。
3.而实际的情况,看看存在索引的情况；
SCOTT@test01p> create index i_t1_id on t1(id);
Index created.
SCOTT@test01p> create index i_t2_id on t2(id);
Index created.
SCOTT@test01p> select * from t1 where id not in (select id from t2 ) and t1.id is not null;
no rows selected
Plan hash value: 1336658214
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 0 |00:00:00.01 | 6 |
|* 1 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 6 |
| 2 | NESTED LOOPS ANTI SNA | | 0 | 1 | 11 | 5 (60)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 0 | 4 | 32 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 4 | INDEX FULL SCAN | I_T1_ID | 0 | 4 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 5 | INDEX RANGE SCAN | I_T2_ID | 0 | 5 | 15 | 0 (0)| | 0 |00:00:00.01 | 0 |
|* 6 | TABLE ACCESS FULL | T2 | 1 | 1 | 3 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 6 |
-------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / T1@SEL$1
4 - SEL$5DA710D3 / T1@SEL$1
5 - SEL$5DA710D3 / T2@SEL$2
6 - SEL$2 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
4 - filter("T1"."ID" IS NOT NULL)
5 - access("ID"="ID")
6 - filter("ID" IS NULL)
--//出现了NESTED LOOPS ANTI SNA，而前面没有索引时是HASH JOIN ANTI NA。
--//这里的ANTI SNA，明显作用T2，注意看id=6的过滤条件是filter("ID" IS NULL)。
--//另外显示的执行计划有点问题，按照以前读执行计划的执行顺序应该是(id)4->3->5->2->6->1.
--//而实际的执行情况是6->4->3->5->2->1. 当id=6的输出存在id is null时,id＝2,3,4,5不会执行。
delete t2 where id is null and name ='t2null';
commit;
SCOTT@test01p> select * from t1 where id not in (select id from t2 ) and t1.id is not null;
ID NAME
---------- --------------------
1 t11
Plan hash value: 1336658214
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 1 |00:00:00.01 | 13 |
|* 1 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 13 |
| 2 | NESTED LOOPS ANTI SNA | | 1 | 1 | 11 | 5 (60)| 00:00:01 | 1 |00:00:00.01 | 6 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 4 | 32 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 4 |
|* 4 | INDEX FULL SCAN | I_T1_ID | 1 | 4 | | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | I_T2_ID | 4 | 5 | 15 | 0 (0)| | 3 |00:00:00.01 | 2 |
|* 6 | TABLE ACCESS FULL | T2 | 1 | 1 | 3 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 7 |
-------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / T1@SEL$1
4 - SEL$5DA710D3 / T1@SEL$1
5 - SEL$5DA710D3 / T2@SEL$2
6 - SEL$2 / T2@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX(@"SEL$5DA710D3" "T1"@"SEL$1" ("T1"."ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5DA710D3" "T1"@"SEL$1")
INDEX(@"SEL$5DA710D3" "T2"@"SEL$2" ("T2"."ID"))
LEADING(@"SEL$5DA710D3" "T1"@"SEL$1" "T2"@"SEL$2")
USE_NL(@"SEL$5DA710D3" "T2"@"SEL$2")
FULL(@"SEL$2" "T2"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
4 - filter("T1"."ID" IS NOT NULL)
5 - access("ID"="ID")
6 - filter("ID" IS NULL)
--//当id=6的查询id is null为0条时,id＝2,3,4,5才会执行。注意看id=6的A-Rows=0.
insert into t2 select rownum+5 ,'t2'||to_char(rownum+5) from dual connect by level<=10000;
insert into t2 values (null,'T2NULL');
commit;
--//注：不要分析表T2，不然执行计划会变。我的测试发现分析即使加入上面的outline也不会选择原来的执行计划。
--//这个问题放在后面再看看。
SCOTT@test01p> select * from t1 where id not in (select id from t2 ) and t1.id is not null;
no rows selected
Plan hash value: 1336658214
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 0 |00:00:00.01 | 24 |
|* 1 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 24 |
| 2 | NESTED LOOPS ANTI SNA | | 0 | 1 | 11 | 5 (60)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 0 | 4 | 32 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 4 | INDEX FULL SCAN | I_T1_ID | 0 | 4 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 5 | INDEX RANGE SCAN | I_T2_ID | 0 | 5 | 15 | 0 (0)| | 0 |00:00:00.01 | 0 |
|* 6 | TABLE ACCESS FULL | T2 | 1 | 1 | 3 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 24 |
-------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / T1@SEL$1
4 - SEL$5DA710D3 / T1@SEL$1
5 - SEL$5DA710D3 / T2@SEL$2
6 - SEL$2 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
4 - filter("T1"."ID" IS NOT NULL)
5 - access("ID"="ID")
6 - filter("ID" IS NULL)
--//我插入表t2的id is null值在最后，导致id=6的逻辑读增加到24。
--//像这类语句如何优化呢？
--//方法1如果表t2的id确实不存在null值，可以加入约束限制id is not null。这样执行计划不会再出现NA或者SNA.
--//方法2如果表t2的id确实存在null值，可以建立包含null值的函数索引看看。先尝试建立函数索引的情况。
SCOTT@test01p> create index if_t2_id on t2(id,0);
Index created.
SCOTT@test01p> insert into t2 values (null,'t2null');
1 row created.
SCOTT@test01p> commit;
Commit complete.
SCOTT@test01p> select count(*) from t2 where id is null;
COUNT(*)
----------
2
--//表T2存在2条id is null。
SCOTT@test01p> select /*+ index(@"SEL$2" "T2"@"SEL$2") */ * from t1 where id not in (select id from t2 ) and t1.id is not null;
no rows selected
Plan hash value: 894503773
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 0 |00:00:00.01 | 2 |
|* 1 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 2 |
| 2 | NESTED LOOPS ANTI SNA | | 0 | 1 | 11 | 8 (75)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 0 | 4 | 32 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 4 | INDEX FULL SCAN | I_T1_ID | 0 | 4 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 5 | INDEX RANGE SCAN | I_T2_ID | 0 | 5 | 15 | 0 (0)| | 0 |00:00:00.01 | 0 |
|* 6 | INDEX RANGE SCAN | IF_T2_ID | 1 | 1 | 3 | 6 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / T1@SEL$1
4 - SEL$5DA710D3 / T1@SEL$1
5 - SEL$5DA710D3 / T2@SEL$2
6 - SEL$2 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
4 - filter("T1"."ID" IS NOT NULL)
5 - access("ID"="ID")
6 - access("ID" IS NULL)
--//id=6,仅仅取1条就ok了。
SCOTT@test01p> delete from t2 where id is null;
2 rows deleted.
SCOTT@test01p> commit;
Commit complete.
SCOTT@test01p> alter table t2 modify(id not null);
Table altered.
SCOTT@test01p> select * from t1 where id not in (select id from t2 ) and t1.id is not null;
ID NAME
---------- --------------------
1 t11
Plan hash value: 1662492778
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 10 |
| 1 | NESTED LOOPS ANTI | | 1 | 1 | 11 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 10 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 4 | 32 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 4 |
|* 3 | INDEX FULL SCAN | I_T1_ID | 1 | 4 | | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 2 |
|* 4 | INDEX RANGE SCAN | I_T2_ID | 4 | 4 | 12 | 0 (0)| | 3 |00:00:00.01 | 6 |
------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T1@SEL$1
4 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."ID" IS NOT NULL)
4 - access("ID"="ID")
--//执行计划变了简单许多，不再出现ANTI SNA或者ANTI NA。
SCOTT@test01p> alter table t2 modify(id null);
Table altered.
SCOTT@test01p> drop index IF_T2_ID;
Index dropped.
--//分析表T2略!!
SCOTT@test01p> select * from t1 where id not in (select id from t2 ) and t1.id is not null;
ID NAME
---------- --------------------
1 t11
Plan hash value: 4053963239
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 11 (100)| | 1 |00:00:00.01 | 33 | | | |
|* 1 | HASH JOIN ANTI NA | | 1 | 1 | 12 | 11 (0)| 00:00:01 | 1 |00:00:00.01 | 33 | 1995K| 1995K| 1044K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 4 | 32 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 2 | | | |
|* 3 | INDEX FULL SCAN | I_T1_ID | 1 | 4 | | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 1 | | | |
| 4 | TABLE ACCESS FULL | T2 | 1 | 10004 | 40016 | 9 (0)| 00:00:01 | 10004 |00:00:00.01 | 31 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T1@SEL$1
4 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="ID")
3 - filter("T1"."ID" IS NOT NULL)
--//这样的执行计划很难看出要建立函数索引包含null值，解决语句的优化问题。
select
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX(@"SEL$5DA710D3" "T1"@"SEL$1" ("T1"."ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5DA710D3" "T1"@"SEL$1")
INDEX(@"SEL$5DA710D3" "T2"@"SEL$2" ("T2"."ID"))
LEADING(@"SEL$5DA710D3" "T1"@"SEL$1" "T2"@"SEL$2")
USE_NL(@"SEL$5DA710D3" "T2"@"SEL$2")
FULL(@"SEL$2" "T2"@"SEL$2")
END_OUTLINE_DATA
*/
* from t1 where id not in (select id from t2 ) and t1.id is not null;
Plan hash value: 4053963239
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 11 (100)| | 1 |00:00:00.01 | 33 | | | |
|* 1 | HASH JOIN ANTI NA | | 1 | 1 | 12 | 11 (0)| 00:00:01 | 1 |00:00:00.01 | 33 | 1995K| 1995K| 1044K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 4 | 32 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 2 | | | |
|* 3 | INDEX FULL SCAN | I_T1_ID | 1 | 4 | | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 1 | | | |
| 4 | TABLE ACCESS FULL | T2 | 1 | 10004 | 40016 | 9 (0)| 00:00:01 | 10004 |00:00:00.01 | 31 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T1@SEL$1
4 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="ID")
3 - filter("T1"."ID" IS NOT NULL)
--//加入提示不起作用。
SCOTT@test01p> create index if_t2_id on t2(id,0);
Index created.
SCOTT@test01p> drop index I_T2_ID;
Index dropped.
SCOTT@test01p> select * from t1 where id not in (select id from t2 ) and t1.id is not null;
ID NAME
---------- --------------------
1 t11
Plan hash value: 2867236616
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 1 |00:00:00.01 | 12 | 1 |
|* 1 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 12 | 1 |
| 2 | NESTED LOOPS ANTI SNA | | 1 | 1 | 12 | 7 (15)| 00:00:01 | 1 |00:00:00.01 | 10 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 4 | 32 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 4 | 0 |
|* 4 | INDEX FULL SCAN | I_T1_ID | 1 | 4 | | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 2 | 0 |
|* 5 | INDEX RANGE SCAN | IF_T2_ID | 4 | 9170 | 36680 | 1 (0)| 00:00:01 | 3 |00:00:00.01 | 6 | 1 |
|* 6 | INDEX RANGE SCAN | IF_T2_ID | 1 | 1 | 4 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | 0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / T1@SEL$1
4 - SEL$5DA710D3 / T1@SEL$1
5 - SEL$5DA710D3 / T2@SEL$2
6 - SEL$2 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
4 - filter("T1"."ID" IS NOT NULL)
5 - access("ID"="ID")
6 - access("ID" IS NULL)
--//仅仅给出一个优化思路，出现这样的情况可以考虑建立1个函数索引，避免全表扫描。
3.总结：
--//1.还是无法搞清在not in的sql语句什么出现ANTI SNA或者ANTI NA，至少说明一点sna作用于一边。
--//2.注意NULL判断,NULL的逻辑仅仅存在 NULL is NULL 是true,NULL is not NULL是false,其它NULL=NULL之类的都表示noknown.
--//3.注意仅仅not in在执行计划中才存在NA,SNA. not exists 不会
--//4.个人建议,使用exists/not exists代替in/not in,可以满足大部分业务与实际查询的需要.甚至可以建议开发应该忘记not in的写法。
--//即使使用not exists也要注意，在实际工作中许多可以使用exists代替，我以前就遇到类似的情况。开发思维是反的，查询里面包含
--//1个状态位，就2个值。完全可以改写成exists，可以很好利用状态位的索引,许多情况下exists的执行比not exists成本要低许多。
--//5.适当给字段加入not null约束,规避一些这样的情况的全表扫描,因为全NULL值,在oracle中的常规索引不记录,导致无法使用索引.
--//也引出另外的优化解决方法，就是建立适当的函数索引。
--//6.以上完全是基于测试的猜测,不知道是否正确.
4.补充测试：
SCOTT@test01p> drop index I_T1_ID;
Index dropped.
SCOTT@test01p> create index if_t1_id on t1(id,0);
Index created.
SCOTT@test01p> select * from t1 where id not in (select id from t2 where t2.id is not null) ;
ID NAME
---------- --------------------
1 t11
Plan hash value: 1968750447
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 11 (100)| | 1 |00:00:00.01 | 37 | | | |
|* 1 | HASH JOIN ANTI SNA | | 1 | 2 | 24 | 11 (0)| 00:00:01 | 1 |00:00:00.01 | 37 | 1995K| 1995K| 996K (0)|
| 2 | TABLE ACCESS FULL | T1 | 1 | 5 | 40 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 7 | | | |
|* 3 | INDEX FAST FULL SCAN| IF_T2_ID | 1 | 10004 | 40016 | 8 (0)| 00:00:01 | 10004 |00:00:00.01 | 30 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="ID")
3 - filter("T2"."ID" IS NOT NULL)
--//这里的sna作用于T1.
SCOTT@test01p> select /*+ index(t1) */ * from t1 where id not in (select id from t2 where t2.id is not null) ;
ID NAME
---------- --------------------
1 t11
Plan hash value: 1750879247
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 10 (100)| | 1 |00:00:00.01 | 32 | | | |
|* 1 | HASH JOIN ANTI SNA | | 1 | 2 | 24 | 10 (0)| 00:00:01 | 1 |00:00:00.01 | 32 | 1995K| 1995K| 992K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 5 | 40 | 2 (0)| 00:00:01 | 5 |00:00:00.01 | 2 | | | |
| 3 | INDEX FULL SCAN | IF_T1_ID | 1 | 5 | | 1 (0)| 00:00:01 | 5 |00:00:00.01 | 1 | | | |
|* 4 | INDEX FAST FULL SCAN | IF_T2_ID | 1 | 10004 | 40016 | 8 (0)| 00:00:01 | 10004 |00:00:00.01 | 30 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T1@SEL$1
4 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="ID")
4 - filter("T2"."ID" IS NOT NULL)
--//这样的执行方式逻辑读有点高.无论我如何加提示，都无法把逻辑读降下来。只能通过约束看看，前提是t1.id没有null值。
SCOTT@test01p> delete from t1 where id is null;
1 row deleted.
SCOTT@test01p> commit;
Commit complete.
SCOTT@test01p> alter table t1 modify(id not null);
Table altered.
SCOTT@test01p> select * from t1 where id not in (select id from t2 where t2.id is not null) ;
ID NAME
---------- --------------------
1 t11
SCOTT@test01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5cv4tdffbwrjn, child number 0
-------------------------------------
select * from t1 where id not in (select id from t2 where t2.id is not
null)
Plan hash value: 4102018781
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 1 |00:00:00.01 | 14 |
| 1 | NESTED LOOPS ANTI | | 1 | 2 | 24 | 8 (0)| 00:00:01 | 1 |00:00:00.01 | 14 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 5 | 40 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 8 |
|* 3 | INDEX RANGE SCAN | IF_T2_ID | 4 | 6669 | 26676 | 1 (0)| 00:00:01 | 3 |00:00:00.01 | 6 |
-------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"="ID")
filter("T2"."ID" IS NOT NULL)
SCOTT@test01p> select /*+ index(t1) */ * from t1 where id not in (select id from t2 where t2.id is not null) ;
ID NAME
---------- --------------------
1 t11
Plan hash value: 1026320075
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 1 |00:00:00.01 | 10 |
| 1 | NESTED LOOPS ANTI | | 1 | 2 | 24 | 7 (0)| 00:00:01 | 1 |00:00:00.01 | 10 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 5 | 40 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 4 |
| 3 | INDEX FULL SCAN | IF_T1_ID | 1 | 5 | | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 2 |
|* 4 | INDEX RANGE SCAN | IF_T2_ID | 4 | 6669 | 26676 | 1 (0)| 00:00:01 | 3 |00:00:00.01 | 6 |
-------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T1@SEL$1
4 - SEL$5DA710D3 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ID"="ID")
filter("T2"."ID" IS NOT NULL)