微課sql最佳化(15)、表的連線方法(4)-關於Hash Join(雜湊連線)

orastar發表於2020-03-04
SQL

1、 原理解釋

    資料庫使用Hash Join處理大表的連線，Hash Join處理的兩個資料集分別稱為構造輸入(build input)和探測輸入(probe input)。解析器使用
小表做為 build input,如果build input能儲存到記憶體中(依賴hash_area_size 引數設定)，則Hash Join是最優的連線方式，詳細步驟如下，
  • 1、以小表做為build input
  • 2、在小表的連線鍵應用Hash函式，如F(x) to the join key。
  • 3、Hash函式返回一個確定的值。如F(1234)會返回一個確定的值
  • 4、返回值做為Hash table的索引
  • 5、獲取資料---在probe input中對連線條件使用Hash函式,判斷該條件是否在Hash table。
  • 6、處理Hash 衝突。-----F(1234)和F(5678)可能存在相同的Hash值。需要使用連結串列或陣列方式進一步解決衝突。
如下圖所示，

                                                           圖1 Hash連 接原理
                                                       圖2 Hash衝突

2、Hash Join特點

1、每張表只掃描一次。
2、使用小表做為build input
3、build input處理完後，才會返回第一條記錄

3、跟TOM大神學習Hash Join影片

提取碼：5zwy

4、 Hash Join示例

刷環境指令碼
點選( 此處 )摺疊或開啟
  1. drop   table   ht . c_cons_hash ;
  2. drop   table  ht . a_amt_hash ;
  3. create   table  ht . c_cons_hash  as   select   *   from  ht . c_cons ;   --建立環境
  4. create   table  ht . a_amt_hash  as   select   *   from  ht . a_amt ;   --建立環境
  5. update  ht . c_cons_hash  set  cons_name = 'Hash_Join'   where   rownum < 5 ;
  6. commit ;
  7. create   index  ht . idx_c_cons_hash_name  on  ht . c_cons_hash ( cons_name ) ;
  8. create   index  ht . idx_a_amt_cons_hash_no  on  ht . a_amt_hash ( cons_no ) ;
  9. exec dbms_stats . gather_table_stats ( 'HT' , 'C_CONS_HASH' ) ;
  10. exec dbms_stats . gather_table_stats ( 'HT' , 'A_AMT_HASH' ) ;
  11. update  ht . c_cons_hash  set  cons_name = 'Hash_Join' ;
  12. commit ;
請最佳化以下語句
select  c.org_name,sum(a.amt)
from ht.c_cons_hash c,ht.a_amt_hash a
where c.cons_no=a.cons_no
and c.cons_name='Hash_Join'
group by c.org_name;
最佳化前執行計劃
set autot trace
@待最佳化SQL
Execution Plan
----------------------------------------------------------
Plan hash value: 3942667065
---------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows   | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |   |     2 |    64 |    18   (6)| 00:00:01 |
|   1 |  HASH GROUP BY           |   |     2 |    64 |    18   (6)| 00:00:01 |
|   2 |   NESTED LOOPS           |   |    12 |   384 |    17   (0)| 00:00:01 |
|   3 |    NESTED LOOPS             |   |    12 |   384 |    17   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| C_CONS_HASH   |     2 |    44 |     3   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN           | IDX_C_CONS_HASH_NAME   |     2 |   |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | IDX_A_AMT_CONS_HASH_NO |     6 |   |     1   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID | A_AMT_HASH   |     6 |    60 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("C"."CONS_NAME"='Hash_Join')
   6 - access("C"."CONS_NO"="A"."CONS_NO")
Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
  61156  consistent gets
   0  physical reads
   0  redo size
876  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
  12  rows processed
最佳化後執行計劃
set autot trace
@待最佳化SQL
select  /*+ use_hash(c,a)*/c.org_name,sum(a.amt)
from ht.c_cons_hash c,ht.a_amt_hash a
where c.cons_no=a.cons_no
and c.cons_name='Hash_Join'
group by c.org_name;
Execution Plan
----------------------------------------------------------
Plan hash value: 4201076277
------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |       2 |      64 |      70   (3)| 00:00:01 |
|   1 |  HASH GROUP BY          |           |       2 |      64 |      70   (3)| 00:00:01 |
|*  2 |   HASH JOIN          |           |      12 |     384 |      69   (2)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| C_CONS_HASH         |       2 |      44 |       3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_C_CONS_HASH_NAME |       2 |         |       1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | A_AMT_HASH         | 59968 |     585K|      65   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CONS_NO"="A"."CONS_NO")
   4 - access("C"."CONS_NAME"='Hash_Join')
Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
334  consistent gets
   0  physical reads
   0  redo size
876  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
  12  rows processed
consistent gets從 61156降低到334，透過本小節的講解和練習示例，希望大家能掌握Hash Join的原理、實現過程及應用場景。
引數文件
《Oracle? Database Performance Tuning Guide 11g Release 2 (11.2)》
《 》

    《Troubleshooting Oracle Performance》



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

相關文章