[20230903]执行计划ANTI SNA和ANTI NA表示什么.txt

来源：这里教程网时间：2026-03-03 18:57:06 作者：

[20230903]执行计划ANTI SNA和ANTI NA表示什么.txt --//在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)

编辑推荐：

返回列表

[20230903]执行计划ANTI SNA和ANTI NA表示什么.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推