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 |
----------------------------------------------------------------------------------

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

转载于:http://blog.itpub.net/28536251/viewspace-2140179/