Oracle的表連線方法（六）

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

 

如果where條件有exists、in或=any運算子+子查詢，則Oracle會將其處理為半連線，執行計劃中對應的關鍵字為SEMI。

 

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

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    10 |   230 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI             |             |    10 |   230 |     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 in (select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    10 |   230 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI             |             |    10 |   230 |     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=any(select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    10 |   230 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI             |             |    10 |   230 |     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 |
--------------------------------------------------------------------------------------------