Oracle表连接方法有四种:
● 排序合并连接(Sort Merge Join)
● 嵌套循环连接(Nested Loops Join)
● 哈希连接(Hash Join)
● 笛卡尔积(Cartesian Product)
嵌套循环连接是Oracle将连接的两个表根据结果集的大小,决定出驱动表和被驱动表。结果集小的作为驱动表,结果集大的作为被驱动表。对于驱动表的每一行,都要与被驱动表的所有行使用连接条件进行匹配。适用于驱动表结果集很小,被驱动表在连接列上有高效索引的表连接。可以用于所有连接条件。对应的执行计划为NESTED LOOPS。
SQL> select e.employee_id,e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id and d.department_id=80;
34 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1492013603
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 1054 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 34 | 1054 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 34 | 510 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
这里DEPARTMENTS表加了条件过滤后结果集只有1行,所以被选为驱动表。
SQL> select e.first_name,e.last_name,e.salary,d.department_name from employees e,departments d where d.department_name IN ('Marketing', 'Sales') and e.department_id = d.department_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1021246405
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 722 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 19 | 722 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 20 | 722 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
这里有2次NESTED LOOPS,是因为Oracle 11g引入了引入了向量I/O(Vector I/O),批量处理多个物理I/O请求来提高嵌套循环连接的效率。
如果驱动表的限制条件的字段上有索引,被驱动表的连接条件的字段上有索引,则使用嵌套循环连接的效率就会很高。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28536251/viewspace-2140231/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28536251/viewspace-2140231/