PLSQL_效能最佳化系列02_Oracle Join關聯

標籤：des   cWeb   style   blog   http   color   io   os   使用   

2014-09-25 BaoXinjian

一、摘要

Oracle三種主要串連方式的比較

1. Hash Join

    (1).概述

        i. 讀取一個表的資料，並將放置到記憶體中，並建立唯一關鍵字的位元影像索引

        ii. 讀取另一個表，和記憶體中表通過Hash演算法進行比較

    (2).適用對象

        i. 大表串連小表

        ii. 兩個大表

2. Nested Loops

    (1).概述

        i. 迴圈外表記錄

        ii. 進行逐個比對和內標的串連是否符合條件

    (2).適用對象

        小表驅動大表，返回較少的結果集

3. Merge Join

    (1).概述

        i. 兩個表進行table access full

        ii. 對table access full的結果進行排序

        iii. 進行merge join對排序結構進行合并

    (2).適用對象

        通過rowid訪問資料

 

當sql訪問多個表時，關聯對sql效率就有很重要的影響。關聯要考慮兩個因素，join的類型和join的次序。

 

二、Join分類

1. Nested Loop Join方式

1.1 適用條件

(1). 關聯少量資料(rows)，返回集小。

(2). 關聯條件能高效訪問第二張表(inner table)。高效訪問的關聯條件如‘=‘，反之非高效的關聯條件如‘!=‘,‘>‘等;inner table（即非驅動表）上要有索引。

因此比較適合OLTP系統，因為OLTP系統中一般返回資料量小，而且表上面索引較多。

1.2 實現步驟

(1). 最佳化器選擇驅動表(driving table)，指定其為outer table

(2). 指定另一張表為inner table(非驅動表)

(3). 根據outer table的每行記錄的關聯欄位，來訪問inner table。如下所示：

NESTED LOOPS

Outer_Loop

Inner_Loop

由於Nested Loop從outer table向inner table查詢，關聯的次序就比較重要了。

1.3 Nexted Loop Join的例子

 

2. Hash Join方式

2.1 適用條件

(1). 僅用於等值關聯equijoin（如=）;

(2). 滿足下列任一條件：

大表關聯

或者小表的大部分記錄參與關聯

2.2 實現機制

(1). 最佳化器選擇較小的表，基於join key構建hash table。（驅動表）

(2). 掃描另外一張較大的表，並在hash table中搜尋關聯線

如果記憶體足夠，小表全部在記憶體中，這種情況是最優的，成本可估算為兩張表各一次全表讀。

如果記憶體不夠，則小表的一部分可以放在temporary tablespace中（Temp資料表空間應足夠大），以儘可能提高io速度。

2.3 Hash Join的例子

 

 

3. Sort merge join

3.1 適用情況

通常情況下hash join效能更好，但如果關聯的資料已經排序或不需排序，則sort merge join效能會更好。

非等值關聯（nonequi join,如<,> ）時很有用，因為sort merge join在返回集很大時比nested loop效能好，而hash join又只能在equijoin中使用。

3.2 實現機制

(1). Sort操作：關聯資料按照關聯欄位進行排序。如果資料本來就是排序的，就不需此操作

(2). Merge操作：經過排序的資料進行merge操作。

需要說明的是，sort merge join沒有driving table的概念

 

4. 笛卡爾串連

無關聯條件，應儘可能避免。

 

5.  Outer Join

5.1 Simple Join的擴充

customers表稱為preserved table,orders表稱為optional table

5.2 Outer Join的擴充

• Left outer join
• Right outer join
• Full outer join

5.3 和普通join相比，outer join也可以是nested loop、hash join、sort merge等。但有一些不同之處：

1. Nested Loop Outer Join中，以preserved table作為驅動表，而不是像普通join基於cost來選擇驅動表。

2. Full Outer Join（equijoin）在11g中，自動使用基於hash join的演算法。執行計畫中出現HASH JOIN FULL OUTER。

可以用HINT:NATIVE_FULL_OUTER_JOIN/NO_NATIVE_FULL_OUTER_JOIN來指定使用或不使用這一演算法。

如果不使用，則Full Outer Jion的執行計畫是Left Outer Join和Right Outer Jion的Union。

 

三、Join次序

基本原則是：記錄少的先關聯，這樣參與後續關聯的記錄數就會少。具體來說：

(1). 選擇能排除掉最多記錄的表作為driving table

(2). 剩餘的表中，選選擇有最好的filter的表（排除最多記錄）作為首先參與關聯的表

(3). 以此類推

看這個例子：

SELECT   info
  FROM   taba a, tabb b, tabc c
 WHERE       a.acol BETWEEN 100 AND 200
         AND b.bcol BETWEEN 10000 AND 20000
         AND c.ccol BETWEEN 10000 AND 20000
         AND a.key1 = b.key1
         AND a.key2 = c.key2;

假設a表經過filter後記錄最少，b次之，c記錄最多。那麼可以用a作為driving table,先與b關聯，最後與c關聯

 

四、使用Hint選擇關聯方式和次序

1. 使用hint指定關聯方式

Oracle最佳化器自動選擇join的方式，但有時不是最優的，開發人員可使用hint來選擇join方式，比較執行效率。

相關的hint有：

• USE_NL,USE_HASH,USE_MERGE
• Exists子句中，HASH_SJ,MERGE_SJ,NL_SJ
• Not in子句中，HASH_AJ,MERGE_AJ，NL_AJ

2. 使用hint指定關聯次序

如果oracle最佳化器選擇的關聯次序不是你所希望的，可以用hint（leading和ordered）來指定。Ordered表示按照sql語句中表出現的先後次序，leading則可任意指定，更為通用。

Leading指定了driving table的選定次序。（在nested loop中,driving table就是outer table，在hash join中，是hash table。）

SELECT /*+ leading (a b c) */info

WHERE a.acol BETWEEN 100 AND 200

AND b.bcol BETWEEN 10000 AND 20000

AND c.ccol BETWEEN 10000 AND 20000

AND a.key1 = b.key1

AND a.key2 = c.key2;

3. Undocumented hint參數:swap_join_inputs

注意，上面例子中，a作為驅動表和b關聯，關連接果作為驅動表，再和c關聯。有時需要改變次序,如下面例子

SELECT /*+ leading (a b c)*/ info

WHERE a.key1 = b.key1

AND b.key2 = c.key2;

假如a 1000條，b 10萬條，c 1萬條。由於a和c表沒有關聯欄位，因此a和b先關聯，再和c關聯。但a關聯b產生2萬條記錄，和c關聯時，希望以c為驅動表，能否實現呢？

在hash_join中可以用oracle的隱含hint參數swap_join_inputs實現：

SELECT /*+ leading (a b c) swap_join_inputs(c) */ info

WHERE a.key1 = b.key1

AND b.key2 = c.key2;

 

********************鮑建立*******************

 

參考：http://blog.itpub.net/18474/viewspace-1060728/

參考：metalink：How to switch the driving table in a hash join [ID 171940.1]

PLSQL_效能最佳化系列02_Oracle Join關聯