oracle+semijoin,Semi join 与anti join

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

----------------------------------------------------------

16recursive calls

0db block gets

943consistent gets

0physical reads

0redo size

319908bytes sent via SQL*Net to client

7376bytes received via SQL*Net from client

638SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)

9544rows 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

----------------------------------------------------------

14recursive calls

0db block gets

911consistent gets

0physical reads

0redo size

319908bytes sent via SQL*Net to client

7376bytes received via SQL*Net from client

638SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

9544rows processed

Anti join

当遇到not exists或not in的时候，oracle会使用anti join的连接方式：

sys@EBANK>select o.object_name,o.object_type from test o where notexists(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

----------------------------------------------------------

4recursive calls

0db block gets

188consistent gets

0physical reads

0redo size

662bytes sent via SQL*Net to client

380bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)

10rows 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

----------------------------------------------------------

120recursive calls

0db block gets

811consistent gets

0physical reads

0redo size

662bytes sent via SQL*Net to client

380bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

2sorts (memory)

0sorts (disk)

10rows processed

将object_type字段添加not null的约束后，执行计划由filter改为了antijoin

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

----------------------------------------------------------

232recursive calls

0db block gets

209consistent gets

0physical reads

0redo size

662bytes sent via SQL*Net to client

380bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

5sorts (memory)

0sorts (disk)

10rows processed