Oracle表连接方法有四种:
● 排序合并连接(Sort Merge Join)
● 嵌套循环连接(Nested Loops Join)
● 哈希连接(Hash Join)
● 笛卡尔积(Cartesian Product)
如果两个表对应的结果集很大,使用排序合并的话,排序操作成本较高;使用嵌套循环的话,则循环次数又很多,需要多次访问被驱动表的结果集;为了提高这种情况下表连接的效率,优化器提供了新的表连接方法,即哈希连接。哈希连接是使用哈希运算来得到结果的表连接方法。只适用于等值连接条件。
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 |
---------------------------------------------------------------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28536251/viewspace-2140311/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28536251/viewspace-2140311/