雜湊連線

5.雜湊連線

當記憶體能夠提供足夠的空間時,雜湊(HASH)連線是Oracle優化器通常的選擇。雜湊連線中，優化器根據統計資訊，首先選擇兩個表中的小表，在記憶體中建立這張表的基於連線鍵的雜湊表;優化器再掃描表連線中的大表，將大表中的資料與雜湊表進行比較，如果有相關聯的資料，則將資料新增到結果集中。

當表連線中的小表能夠完全cache到可用記憶體的時候，雜湊連線的效果最佳。雜湊連線的成本只是兩個表從硬碟讀入到記憶體的成本。

但是，如果雜湊表過大而不能全部cache到可用記憶體時，優化器將會把雜湊表分成多個分割槽，再將分割槽逐一cache到記憶體中。當表的分割槽超過了可用記憶體時，分割槽的部分資料就會臨時地寫到磁碟上的臨時表空間上。因此，分割槽的資料寫磁碟時，比較大的區間(EXTENT)會提高I/O效能。ORACLE推薦的臨時表空間的區間是1MB。臨時表空間的區間大小由UNIFORM SIZE指定。

當雜湊表構建完成後，進行下面的處理：

1) 第二個大表進行掃描

2) 如果大表不能完全cache到可用記憶體的時候，大表同樣會分成很多分割槽

3) 大表的第一個分割槽cache到記憶體

4) 對大表第一個分割槽的資料進行掃描，並與雜湊表進行比較，如果有匹配的紀錄，新增到結果集裡面

5) 與第一個分割槽一樣，其它的分割槽也類似處理。

6) 所有的分割槽處理完後，ORACLE對產生的結果集進行歸併，彙總，產生最終的結果。

當雜湊表過大或可用記憶體有限，雜湊表不能完全CACHE到記憶體。隨著滿足連線條件的結果集的增加，可用記憶體會隨之下降，這時已經CACHE到記憶體的資料可能會重新寫回到硬碟去。如果出現這種情況，系統的效能就會下降。

當連線的兩個表是用等值連線並且表的資料量比較大時，優化器才可能採用雜湊連線。雜湊連線是基於CBO的。只有在資料庫初始化引數HASH_JOIN_ENABLED設為True,並且為引數PGA_AGGREGATE_TARGET設定了一個足夠大的值的時候,Oracle才會使用雜湊邊連線。HASH_AREA_SIZE是向下相容的引數,但在Oracle9i之前的版本中應當使用HASH_AREA_SIZE。當使用ORDERED提示時,FROM子句中的第一張表將用於建立雜湊表。

1.    select a.user_name,b.dev_no   
2. from user_info a, dev_info b  
3.  
4. where a.user_id = b.user_id;  
5.  
6. Plan  
7.  
8. ----------------------------------------------------------  
9.  
10. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=3936  
11.  
12. )  
13.  
14. 1 0 HASH JOIN (Cost=5 Card=82 Bytes=3936)  
15.  
16. 2 1 TABLE ACCESS (FULL) OF 'USER_INFO' (Cost=2 Card=82 Bytes  
17.  
18. =1968)  
19.  
20. 3 1 TABLE ACCESS (FULL) OF 'DEV_INFO' (Cost=2 Card=82 Bytes=  
21.  
22. 1968)  

可以通過在SQL語句中新增HINTS,強制ORACLE優化器產生雜湊連線的執行計劃。

1. select /*+ use_hash(a b)*/ a.user_name,b.dev_no   
2.  
3. from user_info a, dev_info b  
4.  
5. where a.user_id = b.user_id; 

當缺少有用的索引時，雜湊連線比巢狀迴圈連線更加有效。雜湊連線也可能比巢狀迴圈連線更快，因為處理記憶體中的雜湊表比檢索B_樹索引更加迅速。