Oracle 學習之效能最佳化（七）join的實現方式

標籤：hash join nestloop join sortmerge join

  本文討論一下join技術背後的機制。我們知道常用的表串連有如下幾種

• 笛卡爾串連

• 內串連

• 左外串連

• 右外串連

• 全串連

這些sql的寫法，想必大家都很清楚了，那麼這些串連的資料訪問是如何?的呢？

• nested loop
  

我們看如下查詢

SQL> alter session set optimizer_mode=rule;Session altered.SQL> select ename,dname from emp,dept where emp.deptno=dept.deptno;14 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3625962092------------------------------------------------| Id  | Operation     | Name    |------------------------------------------------|   0 | SELECT STATEMENT     |       ||   1 |  NESTED LOOPS     |       ||   2 |   NESTED LOOPS     |       ||   3 |    TABLE ACCESS FULL     | EMP     ||*  4 |    INDEX UNIQUE SCAN     | PK_DEPT ||   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

根據我們之前講的執行計畫解讀，本查詢是這樣實現的：

1. 全表掃描emp表（非阻塞掃描，並不是將資料全部取出，才執行下一步）。

2. 將emp中的資料逐條取出，通過索引PK_DEPT查詢出索引中的rowid，結果集變成（ename，rowid）

3. 將2產生的結果集逐條取出，通過rowid去訪問dept表，結果集變成（ename，dname）

4. 將結果集返回。

這種以迴圈的方式取出資料的join實現方式就叫嵌套迴圈。

此計劃可以用如下邏輯虛擬碼實現

for y in （for x in （select * from emp）loop         index lookup the rowid for x.deptno         output joined record（ename，dept.rowid）       end loop）loop select * from dept where rowid=y.rowid output joined record(ename,dname)end loop

我們把emp表稱之為驅動表（注驅動表與from子句的表順序無關，主要看執行計畫）。

此種串連方式，適用於驅動表返回資料比較少，並且被驅動表dept上deptno列有索引。如果查詢返回n行，那麼dept表將被掃描n次。此串連擅長於從結果集中迅速取出第一行。

• Hash Join

  Hash Join適合處理大型結果集，最佳化器選擇兩個表或者來源資料中比較小的，使用join key在記憶體中建立一個hash table。然後掃描大表，並探查hash表，去發現匹配的記錄。
  小表稱為驅動表，大表稱為探查表

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/71/9B/wKioL1XUrsiwbid2AAHA4Wz1Vxc804.jpg" title="hash join.jpg" alt="wKioL1XUrsiwbid2AAHA4Wz1Vxc804.jpg" />

當hash table能全部放到記憶體中，此種情況最好。如果記憶體中放不下hash table，最佳化器將hash table分區，超出記憶體範圍的分區將被寫到暫存資料表空間中。

我們分兩種情況討論hash join的實現

1. hash table 全部在記憶體裡

  hash table是Oracle根據join key利用一個hash函數將小表分割成多個bucket。hash table建立完成後，Oracle去掃描大表，並且採用相同的hash演算法，將讀入的資料也分割成多個bucket。bucket與bucket之間進行join運算，返回結果。直到大表讀完為止。

2. hash table 不能全部放到記憶體中

 使用演算法，將小表先分區（partition），使得一個partition能載入到記憶體中。將大表也做相同的分 區。表的分區與分區之間按照1的實現方式取出資料。做完一個分區後，再將另一個分區載入進記憶體，繼續相同的操作，直到資料全部處理完。

SQL> insert into big_emp select * from big_emp;SQL> insert into big_emp select * from big_emp;#重複執行多次SQL> /458752 rows created.SQL> create table dept_new as select * from dept;Table created.SQL> set autot traceonlySQL> select * from big_emp a,dept_new b where a.deptno=b.deptno;917504 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1925493178-------------------------------------------------------------------------------| Id  | Operation   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |   917K|    54M|  1490(2)| 00:00:18 ||*  1 |  HASH JOIN   |      |   917K|    54M|  1490(2)| 00:00:18 ||   2 |   TABLE ACCESS FULL| DEPT_NEW |     4 |   120 |     3(0)| 00:00:01 ||   3 |   TABLE ACCESS FULL| BIG_EMP  |   917K|    28M|  1482(1)| 00:00:18 |-------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("A"."DEPTNO"="B"."DEPTNO")Note-----   - dynamic sampling used for this statement (level=2)Statistics----------------------------------------------------------  4  recursive calls  1  db block gets      66338  consistent gets  0  physical reads  0  redo size   62512398  bytes sent via SQL*Net to client     673349  bytes received via SQL*Net from client      61168  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)     917504  rows processed

• Sort Merge Joins

  
  

本文出自 “叮咚” 部落格，請務必保留此出處http://lqding.blog.51cto.com/9123978/1686257

Oracle 學習之效能最佳化（七）join的實現方式