Oracle Join Methods

Hash Join

過程

通過一個例子來說明

SQL_ID  gtmmr9gaxqhfr, child number 0
-------------------------------------
SELECT /*+ USE_HASH(d e) */e.ename, d.dname FROM emp e, dept d where
e.deptno=d.deptno

Plan hash value: 1123238657

-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |        |      |  106 (100)|          |      |      |          |
|*  1 |  HASH JOIN        |      |    15 |  360 |  106  (1)| 00:00:02 |  1206K|  1206K|  661K (0)|
|  2 |  TABLE ACCESS FULL| EMP  |    15 |  135 |    3  (0)| 00:00:01 |      |      |          |
|  3 |  TABLE ACCESS FULL| DEPT |    100K|  1465K|  103  (1)| 00:00:02 |      |      |          |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - access("E"."DEPTNO"="D"."DEPTNO")

1. Oracle將emp以deptno為hash key在PGA中建立一個hash table，這個hash table是分區的，假如PGA的hash area不夠大，將會以分區為單位寫入temp資料表空間。Oracle總是使用較小的結果集建立hash table。在執行計畫中，緊跟著Hash join的就是probed hash table。

 

2. Oracle掃描dept，同樣以deptno位hash key去probe記憶體/temp中的hash table。

3. 返回結果。

由於Hash join使用PGA，是私人記憶體，不需要latch保護。這一點與buffer cache中內容讀寫不一樣。

 

Zero pass, One pass and Multiple pass

hash area size能夠儲存整個probed hash table時，不需要使用temp，效能最優，為zero pass。在執行機會中稱之為0mem

當hash area size能夠放下至少一個分區時，執行計畫中為1mem.

hash area size連一個完整分區都不能儲存時，為最差情況，這種情況稱為multiple pass.

 

Nested Loop Join

Nested loop工作原理類似雙重迴圈。通常用於兩個小的結果集。

特點：

outer table比較小，inner table可以用串連鍵快速定位，比如使用索引。也就是說,inner table的串連鍵上有索引。在執行計畫裡，

NESTED LOOPS
outer_loop
inner_loop

Oracle 11g引入了新的實現方式，在執行計畫裡有體現。具體請看Performance Tuning Guide Page 300。