1.semi join
Oracle在处理exists或in的时候，会使用semi join的连接方式：
sys@EBANK>select object_name,object_type from test where object_type in(select object_type from dept where deptno>1000) ;
9544 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 272673514
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9554 | 335K| 31 |
|* 1 | HASH JOIN RIGHT SEMI| | 9554 | 335K| 31 |
| 2 | VIEW | VW_NSO_1 | 8554 | 94094 | 6 |
|* 3 | TABLE ACCESS FULL | DEPT | 8554 | 200K| 6 |
| 4 | TABLE ACCESS FULL | TEST | 9554 | 233K| 22 |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_TYPE"="$nso_col_1")
3 - filter("DEPTNO">1000)
Note
-----
- cpu costing is off (consider enabling it)
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
16 recursive calls
0 db block gets
943 consistent gets
0 physical reads
0 redo size
319908 bytes sent via SQL*Net to client
7376 bytes received via SQL*Net from client
638 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9544 rows processed
sys@EBANK>select o.object_name,o.object_type from test o where exists(select 1 from dept d where o.object_type=d.object_type and d.deptno>1000);
9544 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1102587590
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9554 | 335K| 31 |
|* 1 | HASH JOIN RIGHT SEMI| | 9554 | 335K| 31 |
| 2 | VIEW | VW_SQ_1 | 8554 | 94094 | 6 |
|* 3 | TABLE ACCESS FULL | DEPT | 8554 | 200K| 6 |
| 4 | TABLE ACCESS FULL | TEST | 9554 | 233K| 22 |
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("O"."OBJECT_TYPE"="OBJECT_TYPE")
3 - filter("D"."DEPTNO">1000)
Note
-----
- cpu costing is off (consider enabling it)
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
911 consistent gets
0 physical reads
0 redo size
319908 bytes sent via SQL*Net to client
7376 bytes received via SQL*Net from client
638 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9544 rows processed
- Anti join
当遇到not exists或not in的时候，oracle会使用anti join的连接方式：
sys@EBANK>select o.object_name,o.object_type from test o where not exists(select 1 from dept d where o.object_type=d.object_type and d.deptno>1000);
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2621105150
-------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 33 |
|* 1 | HASH JOIN RIGHT ANTI| | 1 | 49 | 33 |
|* 2 | TABLE ACCESS FULL | DEPT | 8554 | 200K| 6 |
| 3 | TABLE ACCESS FULL | TEST | 9554 | 233K| 22 |
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("O"."OBJECT_TYPE"="D"."OBJECT_TYPE")
2 - filter("D"."DEPTNO">1000)
Note
-----
- cpu costing is off (consider enabling it)
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
188 consistent gets
0 physical reads
0 redo size
662 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
但在处理not in的时候，如果连接字段可以为null，将无法使用anti join，而只能使用filter操作：
sys@EBANK>select o.object_name,o.object_type from test o where object_type not in(select object_type from dept where deptno>1000);
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2303781323
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 9273 | 226K| 90 |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL| TEST | 9554 | 233K| 22 |
|* 3 | TABLE ACCESS FULL| DEPT | 8126 | 190K| 2 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "DEPT" "DEPT" WHERE
"DEPTNO">1000 AND LNNVL("OBJECT_TYPE"<>:B1)))
3 - filter("DEPTNO">1000 AND LNNVL("OBJECT_TYPE"<>:B1))
Note
-----
- cpu costing is off (consider enabling it)
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
120 recursive calls
0 db block gets
811 consistent gets
0 physical reads
0 redo size
662 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10 rows processed
将object_type字段添加not null的约束后，执行计划由filter改为了anti join
sys@EBANK>alter table test modify (object_type VARCHAR2(19) not null);
Table altered.
sys@EBANK>alter table dept modify (object_type VARCHAR2(19) not null);
Table altered.
sys@EBANK>set autot traceonly
sys@EBANK>select o.object_name,o.object_type from test o where object_type not in(select object_type from dept where deptno>1000);
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2621105150
-------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 33 |
|* 1 | HASH JOIN RIGHT ANTI| | 1 | 49 | 33 |
|* 2 | TABLE ACCESS FULL | DEPT | 8554 | 200K| 6 |
| 3 | TABLE ACCESS FULL | TEST | 9554 | 233K| 22 |
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_TYPE"="OBJECT_TYPE")
2 - filter("DEPTNO">1000)
Note
-----
- cpu costing is off (consider enabling it)
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
232 recursive calls
0 db block gets
209 consistent gets
0 physical reads
0 redo size
662 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
10 rows processed
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-692908/，如需转载，请注明出处，否则将追究法律责任。
