Oracle的表連線方法（一）排序合併連線

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

 

排序合併連線是將連線的兩個表使用連線列排序後，對排序後的結果集進行合併後再得到匹配記錄。如果連線列上面有索引，可以避免排序，那麼最佳化器就有可能會選擇排序合併連線。可以用於=，>，>=，<，<=連線條件，不適用於<>，like連線條件。對應執行計劃為SORT JOIN和MERGE JOIN 。

 

SQL> select e.employee_id,e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id;

106 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1343509718

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   106 |  3286 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |             |   106 |  3286 |     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 JOIN                  |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

 

如果沒有索引，則會選擇雜湊連線：
SQL> create table emp as select * from employees;

Table created.

SQL> create table dept as select * from departments;

Table created.

SQL> select e.employee_id,d.department_name from emp e,dept d where e.department_id=d.department_id;

106 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   106 |  5936 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |   106 |  5936 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |    27 |   810 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |   107 |  2782 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

如果使用不等連線條件，則會選擇巢狀迴圈連線：
SQL> select e.employee_id,d.department_name from employees e,departments d where e.department_id!=d.department_id;

2756 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2968905875

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |  2757 | 63411 |    41   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |             |  2757 | 63411 |    41   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEES   |   102 |   714 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------