oracle hash join原理及注意事項

7.3引入，僅用於CBO和等值連線，隱含引數_hash_join_enabled預設true，即使為false也可被use_hash覆蓋；
Oracle依據hash_area_size/db_block_size/_hash_multiblock_io_count決定hash partition數量，hash表由若干hash partition組成，而每個partition都包含多個hash bucket

表small和big，前者會被選為驅動表，假定其結果集為s，後者為b；
構造驅動表
1 遍歷s，對其每條記錄按照連線列做hash運算；使用2個hash函式，暫稱為func_1/func_2，其hash值為value1和value2；
2 按照value1分配bucket，把s和value2存入其中；只需查詢列/連線列即可，不需要整行記錄；構造hash partition時每個分割槽對應1個bitmap，標註該分割槽所屬bucket是否有記錄；
3 如果s > hash_area_size，將包含記錄最多的partition存入temp，反覆直至完成；
4 按記錄數對hash partition排序，當s > hash_area_size，則儘量將較小的partition保留記憶體中；
構造被驅動表
5 遍歷b，處理方式與步驟1一樣；
6 按照value1匹配s的bucket，然後遍歷其所有記錄，並校驗連線列是否相等；如果成功則返回記錄；如果沒有在記憶體中找到bucket，首先訪問bitmap，若其記錄數>0，暫將b中對應記錄寫入temp，若=0則直接跳過，即點陣圖過濾；反覆直至完成所有位於記憶體中的s和b的記錄；
7 處理temp中的si和bj，分割槽號相等的結成對sn/bn，記錄數較少的作驅動表，以value2構造hash table；反覆直至完成

注意事項
1 驅動表連線列的選擇性要高，若一個bucket的記錄數過多，遍歷時會嚴重消耗CPU且邏輯讀不高(位於PGA中)，可參照http://www.dbafan.com/blog/?p=151
2 驅動表應儘可能小，最好可全部裝入記憶體hash_area_size

10104事件
跟蹤hash join，記錄hash partition/bucket以及每個bucket多少條記錄

Hint
use_hash(table_1 table_2) 與 use_hash(table_1) + use_hash(table_2)等價，即並不決定連線順序；
no_swap_join_inputs(rowsource_alias)和swap_join_inputs(rowsource_alias) 可指定驅動表和被驅動表，後者不可swap；

select  /*+ leading(table_1 table_2) use_hash(table_2) no_swap_join_inputs(table_2) */ *
from    t1 table_1, t2 table_2
where   table_1.n1 = table_1.n1;
-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 45000 |    16M|    44 |
|*  1 |  HASH JOIN         |      | 45000 |    16M|    44 |
|   2 |   TABLE ACCESS FULL| T1   |  3000 |   547K|    14 |
|   3 |   TABLE ACCESS FULL| T2   |  3000 |   547K|    14 |
-----------------------------------------------------------

select  /*+ leading(table_1 table_2) use_hash(table_2) swap_join_inputs(table_2) */ *
from    t1 table_1, t2 table_2
where   table_1.n1 = table_1.n1;

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 45000 |    16M|    44 |
|*  1 |  HASH JOIN         |      | 45000 |    16M|    44 |
|   2 |   TABLE ACCESS FULL| T2   |  3000 |   547K|    14 |
|   3 |   TABLE ACCESS FULL| T1   |  3000 |   547K|    14 |
-----------------------------------------------------------

 

參考資料