【sql調優之執行計劃】hash join

在大表和小表做join時，可能會使用到hash join的連線方式。其原理是將兩個表中的一張表（較小的）通過hash 演算法裝入記憶體（如果記憶體不夠，則使用臨時表空間），然後再讀大表資料，讀取它的記錄，計算hash值，與記憶體中的hash值匹配。有關的cost大致是以這樣的公式來計算的：

cost = (outer access cost * # of hash partitions) + inner access cost

inner table就是裝入記憶體的小表，hash partions是小表的hash分割槽數。外表（大表）的行從內表（小表）的hash分割槽中匹配hash鍵值。

Hash join只在cbo下有效，等值連線情況下，當一個表有大量的資料需要join而另一個表只有一小部分需要join的時候使用，可以看一個例子：

SQL> select /*+ use_hash(a,b)*/b.*

2 from scott.dept b,scott.emp a

3 where a.deptno = b.deptno

4 ;

DEPTNO DNAME LOC

---------- -------------- -------------

20 RESEARCH DALLAS

30 SALES CHICAGO

20 RESEARCH DALLAS

30 SALES CHICAGO

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

10 ACCOUNTING NEW YORK

30 SALES CHICAGO

20 RESEARCH DALLAS

DEPTNO DNAME LOC

---------- -------------- -------------

30 SALES CHICAGO

20 RESEARCH DALLAS

13 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 615168685

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 322 | 7 (15)| 00:00:01 |

|* 1 | HASH JOIN | | 14 | 322 | 7 (15)| 00:00:01 |

| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("A"."DEPTNO"="B"."DEPTNO")

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

11 consistent gets

0 physical reads

0 redo size

820 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

13 rows processed

SQL>

如果hash table太大而不能在記憶體中裝下，那麼優化器將table劃分為多個不同的分割槽，記憶體不能放下的分割槽，將寫入磁碟，使用臨時段。這也是hash join當需要hash的table大到一定程度時效率顯著下降的原因，大量磁碟io帶來了效能上的問題。

看看執行計劃中的這一行：

1 - access("A"."DEPTNO"="B"."DEPTNO")

可以知道dept表的hash key是deptno，其實這個key的選擇性不是很好，oracle優化器預設也沒有使用hash，故而使用了hint強制使用hash join。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-671331/，如需轉載，請註明出處，否則將追究法律責任。

【sql調優之執行計劃】hash join

相關文章