WINDOWS下的程序员出身，偶尔也写一些linux平台下小程序， 后转行数据库行业，专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群：58308065，23618606
全部博文（599）
分类： Oracle
2013-03-22 13:22:48
NESTED LOOPS ANTI
表示外层循环读取到的数据在内层循环中匹配到，内层循环将会里面终止，并且抛弃该条数据，开始下一轮的外层循环。
常用于not in 和 not exists情况下。
SQL> select * from t where object_id not in (select object_id from t_object);
Execution Plan
----------------------------------------------------------
Plan hash value: 4241754024
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 | 103 (0)| 00:00:02 |
| 1 | NESTED LOOPS ANTI | | 1 | 92 | 103 (0)| 00:00:02 |
| 2 | TABLE ACCESS FULL| T | 100 | 8700 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T_OBJECT_01 | 145K| 710K| 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"="OBJECT_ID")
SQL> select * from t where not exists(select 1 from t_object where t_object.object_id=t.object_id);
Execution Plan
----------------------------------------------------------
Plan hash value: 4241754024
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 | 103 (0)| 00:00:02 |
| 1 | NESTED LOOPS ANTI | | 1 | 92 | 103 (0)| 00:00:02 |
| 2 | TABLE ACCESS FULL| T | 100 | 8700 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T_OBJECT_01 | 145K| 710K| 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T_OBJECT"."OBJECT_ID"="T"."OBJECT_ID")
并且在not exists下_unnest_subquery 需要设置为TRUE，如果为FALSE ，执行计划可能走不到NESTED LOOPS ANTI。
SQL> alter session set "_unnest_subquery"=false;
Session altered.
SQL>
SQL> select * from t where not exists(select 1 from t_object where t_object.object_id=t.object_id);
Execution Plan
----------------------------------------------------------
Plan hash value: 2212308907
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 8613 | 53 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T | 100 | 8700 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T_OBJECT_01 | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "T_OBJECT" "T_OBJECT" WHERE
"T_OBJECT"."OBJECT_ID"=:B1))
3 - access("T_OBJECT"."OBJECT_ID"=:B1)
NESTED LOOPS SEMI
表示如果外层循环读取到的数据在内层循环匹配到，内层循环将立马终止，并且返回该条数据，开始下一轮的外层循环。
常用于 in 和 exists情况下。
SQL> select * from t where object_id in (select object_id from t_object);
Execution Plan
----------------------------------------------------------
Plan hash value: 2048262264
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 9200 | 103 (0)| 00:00:02 |
| 1 | NESTED LOOPS SEMI | | 100 | 9200 | 103 (0)| 00:00:02 |
| 2 | TABLE ACCESS FULL| T | 100 | 8700 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T_OBJECT_01 | 146K| 717K| 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"="OBJECT_ID")
SQL> select * from t where exists(select 1 from t_object where t_object.object_id=t.object_id);
Execution Plan
----------------------------------------------------------
Plan hash value: 2048262264
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 9200 | 103 (0)| 00:00:02 |
| 1 | NESTED LOOPS SEMI | | 100 | 9200 | 103 (0)| 00:00:02 |
| 2 | TABLE ACCESS FULL| T | 100 | 8700 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T_OBJECT_01 | 146K| 717K| 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T_OBJECT"."OBJECT_ID"="T"."OBJECT_ID")
EXISTS 并不受到 _unnest_subquery 的影响。
SQL> alter session set "_unnest_subquery"=false;
Session altered.
SQL> select * from t where exists(select 1 from t_object where t_object.object_id=t.object_id);
Execution Plan
----------------------------------------------------------
Plan hash value: 2048262264
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 9200 | 103 (0)| 00:00:02 |
| 1 | NESTED LOOPS SEMI | | 100 | 9200 | 103 (0)| 00:00:02 |
| 2 | TABLE ACCESS FULL| T | 100 | 8700 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T_OBJECT_01 | 146K| 717K| 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T_OBJECT"."OBJECT_ID"="T"."OBJECT_ID")
