ORACLE 半连接与反连接

概念：所谓半连接，就是在进行连接查询的时候，内层如果有相应的记录及返回一个TRUE，而不需要访问余下的行，如果内层表特别巨大的时候将会大大节省时间。
列子：
 select /* using in */ department_name
      from hr.departments dept
      where department_id in (select department_id from hr.employees emp)
 
============
Plan Table
============
-----------------------------------------------+-----------------------------------+
| Id  | Operation           | Name             | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |                  |       |       |     3 |           |
| 1   |  NESTED LOOPS SEMI  |                  |    10 |   190 |     3 |  00:00:01 |
| 2   |   TABLE ACCESS FULL | DEPARTMENTS      |    27 |   432 |     3 |  00:00:01 |
| 3   |   INDEX RANGE SCAN  | EMP_DEPARTMENT_IX|    41 |   123 |     0 |           |
-----------------------------------------------+-----------------------------------+
使用范围：in和exists操作，实际上很多时候in和exists是等价的，因为他们的执行计划完全一样，所以一般情况下不用去纠结用in还是exists
执行计划：出现SEMI则说明使用了半连接
控制使用半连接：
1、HINT
seminjion -强制使用半连接
no_semijion-不使用半连接
nl_sj-进行嵌套半连接
hash_sj-进行散列半连接
merge_sj-进行排序半连接
使用如下：
 select department_name
      from hr.departments dept
     where department_id in (select /*+ no_semijoin */ department_id from hr.employees emp);
2、系统参数限制
_always_semi_join：默认为choose，允许优化器对所有的半连接方法进行评估并选择它认为最高效的方法。如果指定为hash,
                   merge或者nested_loops就将优化器的选择限定为所指定的连接方式，OFF则禁用了半连接


半连接限制：优化器不会为任何包含在OR分支中的子查询使用半连接，包含DISTINCT关键字可能禁用半连接，如：
select /* using in */ department_name
      from hr.departments dept
      where 1=1 or department_id in (select department_id from hr.employees emp)
半连接条件：
1、语句必须使用in(=any)或exists
2、语句必须在in或exists中有子查询
3、使用exists必须使用关联子查询
4、in或者exists字句不能再OR分支中


另外还有反连接，其原理和半连接一样，在not in 和 not exists中使用，其执行计划中包含ANTI，但是有一点要注意，如果NOT IN 的时候子查询返回有NULL值
则整个查询返回为空。
实际如下：
select department_name
  from hr.departments dept
 where not exists (select 1
          from hr.employees emp
         where emp.department_id = dept.department_id);


select department_name
  from hr.departments dept
 where department_id  not in (select department_id from hr.employees emp where  department_id is not null );
 
是等价的。
执行计划：
--------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    17 |   323 |     3   (0)| 00
|   1 |  NESTED LOOPS ANTI |                   |    17 |   323 |     3   (0)| 00
|   2 |   TABLE ACCESS FULL| DEPARTMENTS       |    27 |   432 |     3   (0)| 00
|*  3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    41 |   123 |     0   (0)| 00


控制使用反连接：
1、HINT
ANTIJOIN -强制使用反连接
nl_aj-进行嵌套反连接
hash_aj-进行散列反连接
merge_aj-进行排序反连接
注意一下这里没有NO_ANTIJION语法
2、_always_anti_jion
可以这样来取消反连接
alter session set "_always_anti_join"=off;
取值同半连接
反连接条件：
1、语句必须使用NOT IN (!=ALL) 或NOT EXISTS
2、语句必须在NOT IN或NOT EXISTS子句有一个子查询
3、NOT IN或NOT EXISTS子句不能包含在OR分支中
4、NOT EXISTS子句中的子查询必须与外层查询相关
5、注意NOT IN子查询不要返回空值

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-1162940/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-1162940/