Oracle表連線操作——Hash Join（雜湊連線）上

 

在Oracle中，確定連線操作型別是執行計劃生成的重要方面。各種連線操作型別代表著不同的連線操作演算法，不同的連線操作型別也適應於不同的資料量和資料分佈情況。

 

 

無論是Nest Loop Join（巢狀迴圈），還是Merge Sort Join（合併排序連線），都是適應於不同特殊情況的古典連線方法。Nest Loop Join演算法雖然可以藉助連線列索引，但是帶來的隨機讀成本過大。而Merge Sort Join雖然可以減少隨機讀的情況，但是帶來的大規模Sort操作，對記憶體和Temp空間壓力過大。兩種演算法在處理海量資料的時候，如果是海量隨機讀還是海量排序，都是不能被接受的連線演算法。本篇中，我們介紹目前比較常用的一種連線方式Hash Join連線。

 

 

1、Hash Join（雜湊連線）原理

 

從Oracle 7.3開始，Hash Join正式進入最佳化器執行計劃生成，只有CBO才能使用Hash Join操作。本質上說，Hash Join連線是藉助Hash演算法，連帶小規模的Nest Loop Join，同時利用記憶體空間進行高速資料快取檢索的一種演算法。

 

下面我們分步驟介紹Hash Join演算法步驟：

 

   i.        Hash Join連線物件依然是兩個資料表，首先選擇出其中一個“小表”。這裡的小表，就是參與連線操作的資料集合資料量小。對連線列欄位的所有資料值，進行Hash函式操作。Hash函式是電腦科學中經常使用到的一種處理函式，利用Hash值的快速搜尋演算法已經被認為是成熟的檢索手段。Hash函式處理過的資料特徵是“相同資料值的Hash函式值一定相同，不同資料值的Hash函式值可能相同”；

 ii.        經過Hash處理過的小表連線列，連同資料一起存放到Oracle PGA空間中。PGA中存在一塊空間為hash_area，專門存放此類資料。並且，依據不同的Hash函式值，進行劃分Bucket操作。每個Bucket中包括所有相同hash函式值的小表資料。同時建立Hash鍵值對應點陣圖。

iii.        之後對進行Hash連線大表資料連線列依次讀取，並且將每個Hash值進行Bucket匹配，定位到適當的Bucket上（應用Hash檢索演算法）；

iv.        在定位到的Bucket中，進行小規模的精確匹配。因為此時的範圍已經縮小，進行匹配的成功率精確度高。同時，匹配操作是在記憶體中進行，速度較Merge Sort Join時要快很多；

 

下面是一個Hash Join的執行計劃。

 

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 779051904

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

| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |       |  2617 |   572K|   142   (1)| 00:00:02 |

|*  1 |  HASH JOIN         |       |  2617 |   572K|   142   (1)| 00:00:02 |

|   2 |   TABLE ACCESS FULL| SEGS  |  2503 |   312K|    16   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| OBJTS | 31083 |  2914K|   126   (1)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - access("SEGS"."SEGMENT_NAME"="OBJTS"."OBJECT_NAME")

 

 

從原理過程來看，Hash Join與Nest Loop Join/Merge Sort Join存在一定相似度。

 

首先，Hash Join同Nest Loop Join一樣，進行一定的巢狀迴圈匹配操作，不過差異在於匹配進行隨機讀的範圍是受限範圍。不會像Nest Loop Join一樣直接頻繁進行全表規模的隨機讀。

 

其次，Hash Join同之前介紹過的Merge Sort Join有相似點，都是利用PGA的空間進行獨立操作。Hash Join中的Bucket就是儲存在記憶體的PGA中，有一塊專門Hash_Area進行該項操作。選擇小表作為驅動連線表，就是儘量爭取PGA記憶體中可以完全裝下小表資料，儘量不要使用Temp表空間。這樣，進行Hash匹配和精確匹配的速度就是有保證的。

 

 

最後，Hash Join使用的場景是有限制的。其中最大的一個就是連線操作僅能使用“=”連線。因為Hash匹配的過程只能支援相等操作。還有就是連線列的資料分佈要儘量做到資料分佈均勻，這樣產生的Bucket也會盡可能均勻。這樣限制匹配的速度才有保證。如果資料列分佈偏移嚴重，Hash Join演算法效率會有退化趨勢。

 

隨著系統資料量的不斷增加，出現Hash Join的場景就會越來越多。下面透過一系列實驗來確定Hash Join的各種特性。

 

 

2、Hash Join連線實驗

 

首先是準備實驗環境。

 

SQL> create table segs as select * from dba_segments where wner='SYS';

Table created

 

SQL> create table objts as select * from dba_objects where wner='SYS';

Table created

 

SQL> select count(*) from segs;

  COUNT(*)

----------

      2503

 

SQL> select count(*) from objts;

  COUNT(*)

----------

     31083

 

SQL> create index idx_segs_name on segs(segment_name);

Index created

 

SQL> create index idx_objts_name on objts(object_name);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'SEGS',cascade => true);

PL/SQL procedure successfully completed

 

SQL> exec dbms_stats.gather_table_stats(user,'OBJTS',cascade => true);

PL/SQL procedure successfully completed

 

 

此時，我們對比三種連線方式的成本因素。

 

 

SQL> set autotrace traceonly;

SQL> select * from segs, objts where segs.segment_name=objts.object_name;

已選擇4870行。

 

執行計劃

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

Plan hash value: 779051904

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

| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |       |  2617 |   572K|   142   (1)| 00:00:02 |

|*  1 |  HASH JOIN         |       |  2617 |   572K|   142   (1)| 00:00:02 |

|   2 |   TABLE ACCESS FULL| SEGS  |  2503 |   312K|    16   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| OBJTS | 31083 |  2914K|   126   (1)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - access("SEGS"."SEGMENT_NAME"="OBJTS"."OBJECT_NAME")

統計資訊

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

          1  recursive calls

          0  db block gets

        814  consistent gets

          0  physical reads

          0  redo size

     356347  bytes sent via SQL*Net to client

       3940  bytes received via SQL*Net from client

        326  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       4870  rows processed

 

SQL> select /*+use_nl(segs,objts)*/* from segs, objts where segs.segment_name=objts.object_name;

 

已選擇4870行。

 

執行計劃

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

Plan hash value: 2045044449

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

| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |                |  2617 |   572K|  5023   (1)| 00:01:01 |

|   1 |  NESTED LOOPS                |                |       |       |            |          |

|   2 |   NESTED LOOPS               |                |  2617 |   572K|  5023   (1)| 00:01:01 |

|   3 |    TABLE ACCESS FULL         | SEGS           |  2503 |   312K|    16   (0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN          | IDX_OBJTS_NAME |     1 |       |     1   (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| OBJTS          |     1 |    96 |     2   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

   4 - access("SEGS"."SEGMENT_NAME"="OBJTS"."OBJECT_NAME")

 

統計資訊

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

          1  recursive calls

          0  db block gets

       5799  consistent gets

          0  physical reads

          0  redo size

     406352  bytes sent via SQL*Net to client

       3940  bytes received via SQL*Net from client

        326  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       4870  rows processed

 

SQL> select /*+use_merge(segs,objts)*/* from segs, objts where segs.segment_name=objts.object_name;

已選擇4870行。

執行計劃

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

Plan hash value: 2272228973

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

| Id  | Operation           | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |       |  2617 |   572K|       |   900   (1)| 00:00:11 |

|   1 |  MERGE JOIN         |       |  2617 |   572K|       |   900   (1)| 00:00:11 |

|   2 |   SORT JOIN         |       |  2503 |   312K|   920K|    90   (2)| 00:00:02 |

|   3 |    TABLE ACCESS FULL| SEGS  |  2503 |   312K|       |    16   (0)| 00:00:01 |

|*  4 |   SORT JOIN         |       | 31083 |  2914K|  8168K|   809   (1)| 00:00:10 |

|   5 |    TABLE ACCESS FULL| OBJTS | 31083 |  2914K|       |   126   (1)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   4 - access("SEGS"."SEGMENT_NAME"="OBJTS"."OBJECT_NAME")

       filter("SEGS"."SEGMENT_NAME"="OBJTS"."OBJECT_NAME")

 

統計資訊

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

          1  recursive calls

          0  db block gets

        494  consistent gets

          0  physical reads

          0  redo size

     427743  bytes sent via SQL*Net to client

       3940  bytes received via SQL*Net from client

        326  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

       4870  rows processed

 

 

詳細對比見下圖：

 

 

三種連線方式，SQL資料量、語句相同，最後獲取不同的成本消耗。可以看出，當資料量達到萬級之後，Nest Loop Join的隨機讀會急劇增加，帶來的CPU成本和總執行時間成本也會大大增加。

 

而使用Merge Sort Join帶來的塊讀是相對較少，但是付出的CPU成本和執行時間也是不可忽視的。將資料集合排序對映到記憶體中（可能要利用Temp Tablespace），需要消耗很大的CPU和記憶體資源（排序段）。

 

總體來說，Hash Join在這個SQL中還是能帶來很好的綜合效能的。只有塊讀稍大，其他指標都是可以接受的最好值。

 

下面我們介紹與Hash Join相關的一些系統引數，和Hash Join進行的三種操作模式。不同的系統引數，可能會給CBO成本運算帶來影響。不同的操作模式，幫助我們理解PGA中的hash_area大小是如何影響到Hash Join操作的效能。