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子查詢不要返回空值