Oracle中的半联结和反联结详解
当两张表进行联结的时候，如果表1中的数据行是否出现在结果集中需要根据表2中出现或不出现至少一个相匹配的数据行来判断，这种情况就会发生半联结；而反联结便是半联结的补集，它们会作为数据库中常见的联结方法如NESTEDLOOPS，MERGESORTJOIN，HASHJOIN的选项出现。
实际上半联结和反联结本身也可以被认同是两种联结方法；在CBO优化模式下，优化器能够根据实际情况灵活的转换执行语句从而实现半联结和反联结方法，毕竟没有什么SQL语法可以显式的调用半联结和反联结，它们只是SQL语句满足某些条件时优化器可以选择的选项而已，不过仍然有必要深入这两种选项在特定情况下带来的性能优势。
半联结
半联结通常都发生在使用含有IN和EXISTS的相关子查询的时候，=ANY的用法与IN相同，所以也会出现发生半联结的情况；不过也是有例外的，在11gR2版本中，优化器不会为任何包含在OR分支中的子查询选择半联结，这也是现在官档中唯一明确标识的限制条件，来看几种发生场景：
--使用IN关键字的相关子查询=>发生NESTEDLOOPS半联结 SQL>selectdepartment_name 2 fromhr.departmentsdept 3 wheredepartment_idin(selectdepartment_idfromhr.employeesemp);
11rowsselected.
ExecutionPlan ---------------------------------------------------------- Planhashvalue:2605691773
---------------------------------------------------------------------------------------- |Id |Operation |Name |Rows |Bytes|Cost(%CPU)|Time | ---------------------------------------------------------------------------------------- | 0|SELECTSTATEMENT | | 10| 190| 3 (0)|00:00:01| | 1| NESTEDLOOPSSEMI| | 10| 190| 3 (0)|00:00:01| | 2| TABLEACCESSFULL|DEPARTMENTS | 27| 432| 3 (0)|00:00:01| |* 3| INDEXRANGESCAN|EMP_DEPARTMENT_IX| 41| 123| 0 (0)|00:00:01| ----------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid): ---------------------------------------------------
3-access("DEPARTMENT_ID"="DEPARTMENT_ID")
Statistics ---------------------------------------------------------- 0 recursivecalls 0 dbblockgets 11 consistentgets 0 physicalreads 0 redosize 742 bytessentviaSQL*Nettoclient 524 bytesreceivedviaSQL*Netfromclient 2 SQL*Netroundtripsto/fromclient 0 sorts(memory) 0 sorts(disk) 11 rowsprocessed
--使用EXISTS关键字的相关子查询=>发生NESTEDLOOPS半联结 SQL>selectdepartment_name 2 fromhr.departmentsdeptwhereexists 3 (selectnullfromhr.employeesempwhereemp.department_id=dept.department_id);
11rowsselected.
ExecutionPlan ---------------------------------------------------------- Planhashvalue:2605691773
---------------------------------------------------------------------------------------- |Id |Operation |Name |Rows |Bytes|Cost(%CPU)|Time | ---------------------------------------------------------------------------------------- | 0|SELECTSTATEMENT | | 10| 190| 3 (0)|00:00:01| | 1| NESTEDLOOPSSEMI| | 10| 190| 3 (0)|00:00:01| | 2| TABLEACCESSFULL|DEPARTMENTS | 27| 432| 3 (0)|00:00:01| |* 3| INDEXRANGESCAN|EMP_DEPARTMENT_IX| 41| 123| 0 (0)|00:00:01| ----------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid): ---------------------------------------------------
3-access("EMP"."DEPARTMENT_ID"="DEPT"."DEPARTMENT_ID")
Statistics ---------------------------------------------------------- 1 recursivecalls 0 dbblockgets 11 consistentgets 0 physicalreads 0 redosize 742 bytessentviaSQL*Nettoclient 524 bytesreceivedviaSQL*Netfromclient 2 SQL*Netroundtripsto/fromclient 0 sorts(memory) 0 sorts(disk) 11 rowsprocessed
--谓语中使用了OR分支中的EXISTS子查询=>禁用半联结 SQL>selectdepartment_name 2 fromhr.departmentsdept 3 where1=2ORexists 4 (selectnullfromhr.employeesempwhereemp.department_id=dept.department_id);
11rowsselected.
ExecutionPlan ---------------------------------------------------------- Planhashvalue:440241596
---------------------------------------------------------------------------------------- |Id |Operation |Name |Rows |Bytes|Cost(%CPU)|Time | ---------------------------------------------------------------------------------------- | 0|SELECTSTATEMENT | | 27| 432| 4 (0)|00:00:01| |* 1| FILTER | | | | | | | 2| TABLEACCESSFULL|DEPARTMENTS | 27| 432| 3 (0)|00:00:01| |* 3| INDEXRANGESCAN|EMP_DEPARTMENT_IX| 2| 6| 1 (0)|00:00:01| ----------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid): ---------------------------------------------------
1-filter(EXISTS(SELECT0FROM"HR"."EMPLOYEES""EMP"WHERE "EMP"."DEPARTMENT_ID"=:B1)) 3-access("EMP"."DEPARTMENT_ID"=:B1)
Statistics ---------------------------------------------------------- 1 recursivecalls 0 dbblockgets 35 consistentgets 0 physicalreads 0 redosize 742 bytessentviaSQL*Nettoclient 524 bytesreceivedviaSQL*Netfromclient 2 SQL*Netroundtripsto/fromclient 0 sorts(memory) 0 sorts(disk) 11 rowsprocessed