Oracle錶鏈接方法有四種:
● 排序合併鏈接(Sort Merge Join)
● 嵌套循環鏈接(Nested Loops Join)
● 哈希鏈接(Hash Join)
● 笛卡爾積(Cartesian Product) oop
嵌套循環鏈接是Oracle將鏈接的兩個表根據結果集的大小,決定出驅動表和被驅動表。結果集小的做爲驅動表,結果集大的做爲被驅動表。對於驅動表的每一行,都要與被驅動表的全部行使用鏈接條件進行匹配。適用於驅動表結果集很小,被驅動表在鏈接列上有高效索引的錶鏈接。能夠用於全部鏈接條件。對應的執行計劃爲NESTED LOOPS。 spa
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; .net
34 rows selected. blog
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; hash
36 rows selected. it
Execution Plan
----------------------------------------------------------
Plan hash value: 1021246405 io
--------------------------------------------------------------------------------------------------
| 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/