Oracle的表連線方法（七）

Oracle表連線方法有四種：
  ● 排序合併連線（Sort Merge Join）
  ● 巢狀迴圈連線（Nested Loops Join）
  ● 雜湊連線（Hash Join）
  ● 笛卡爾積（Cartesian Product）

如果where條件有not exists、not in或<>all運算子+子查詢，則Oracle會將其處理為反連線，，執行計劃中對應的關鍵字為anti。

SQL> select department_id,department_name from departments d where not exists (select 1 from employees e where d.department_id = e.department_id and e.salary > 2500);

16 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3822487693

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    17 |   391 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN ANTI             |             |    17 |   391 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |             |   105 |   735 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |   105 |   735 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

SQL> select department_id,department_name from departments d where department_id not in (select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);

16 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3822487693

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    17 |   391 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN ANTI             |             |    17 |   391 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |             |   105 |   735 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |   105 |   735 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

 

SQL> select department_id,department_name from departments d where department_id<>all(select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);

16 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3822487693

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    17 |   391 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN ANTI             |             |    17 |   391 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |             |   105 |   735 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |   105 |   735 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------