連線和半連線

n-lauren發表於2014-10-01
連線
ORACLE多表連線分為三大類：NEXT LOOP、SORT MERGE、HASH JOIN。
每一類又分為三小類，有傳統連線，Semi Join， Anti Join。（後兩種叫做半連線）
 
NEST LOOP方式：
有兩個表，驅動表Driving Table，被驅動表Driven Table。
驅動表做一次遍歷，被驅動表做多次遍歷。
返回第一條記錄速度很快，不需要排序。
可以使用非等值連線。
 
SORT MERGE方式：
兩個表地位一樣。每個表都要先排序，然後進行合併，返回記錄集。
排序首先在記憶體中進行，能在記憶體中完成的叫做Optimal Sort，也叫In-Memory Sort。如果需要藉助磁碟緩衝，叫做外部排序External Sort。
 
在外部排序中，執行run是指一次對磁碟做IO。
如果一次輸入就能完成整個資料集的排序叫做1路排序1-Pass Sort。需要多次輸入輸出操作的叫多路排序Multi-Pass Sort。
從效能角度來看Optimal Sort>1-Pass Sort>Multi-Pass Sort
執行計劃中
 
OMem：代表使用Optimal排序需要的記憶體估量。
1Mem：代表使用1-Pass排序需要的記憶體估量。
O/1/M：代表實際Optimal、1-Pass、Multi-Pass方式的執行次數。
 
HASH JOIN方式：
一個驅動表，一個被驅動表。過程有兩個階段：
準備階段：對驅動表的連線欄位進行雜湊操作，產生一系列的Hash Bucket（雜湊桶）
探測階段：依次上去被驅動表每條記錄，對連線欄位執行相同雜湊函式，和驅動表雜湊桶進行匹配，這個過程叫探測（Probe）。
 
幾種方式比較：
ORACLE實現排序都是用二叉樹插入排序演算法（Binary Insertion Tree）。
記憶體中的INDEX中，每個節點對應一條記錄，每個節點還儲存一個父節點和兩個子節點的指標。這樣在32位系統中，這個開銷是12位元組，64位系統中，這個開銷是24位元組。
排序過程是記憶體和CPU的雙重密集操作。
完全記憶體排序有時候不必磁碟排序快。
如果CPU是資源瓶頸，IO比較空閒，應該減少排序空間大小，使用1-Pass Sort。尤其是在建立索引時，通過減少SORT_AREA_SIZE來提升效能。因為記憶體排序和磁碟排序，記錄比較操作相差不大，但是記憶體排序中，二叉樹可能過高，CPU資源消耗太大。
HASH JOIN記憶體消耗遠小於SORT MERGE，也不需要密集的CPU操作。所以HASH JOIN演算法普遍優於SORT MERGE演算法。
如果查詢關注的是整個記錄而不暢部分記錄時，HASH JOIN非常類似NEST LOOP，但優於NEST LOOP，因為HASH TABLE構建在PGA中，不需要LATCH保護。
 
半連線
是針對IN, EXISTS, NOT IN, NOT EXISTS的變形。
子查詢在FROM裡的叫做IN-LINE VIEW，在WHERE子句中的叫NESTED SUBQUERY（巢狀子查詢）。IN, EXISTS, NOT IN, NOT EXISTS都屬於巢狀子查詢。
 
對於巢狀子查詢，ORACLE處理有兩種方式：展開子查詢，不展開子查詢。
對於巢狀檢視，ORACLE處理方法有兩種，合併，不合並。
 
ORACLE 10G以前的優化器會在Optimization之前就展開，不做成本評估。
 
In、Exists展開結果是變成Semi-Join。Not Exists和Not In是轉換成Anti-Join。
對於Inline-View或者其它View，Oracle也會嘗試合併到主查詢中，這個動作叫做Merge，對應hint是和。這個在執行計劃中進行確認就可以。也就是，如果沒有VIEW字樣，就是發生了MERGE合併；有VIEW字樣，就是沒有做MERGE合併。
對於子查詢展開，這個過程叫做Subquery Unnesting。
Merge和Unnest不同的地方是，對於Distinct、Group by這些子句，Merge可以合併，叫做Complex View Merge，Set和Unnest一樣，不能合併。
預設時，不進行Complex View Merge。使用才能達到Merge效果。
 
子查詢合併到主查詢中，好處是優化器可以通判考慮訪問路徑方式。否則，ORACLE只能針對外層記憶體查詢分別優化。而且可以利用ORACLE提供的Semi-Join、Anti-Join兩種連線方式。
不是所有子查詢都可以展開，例如，connect by, start with, rownum偽列, set操作符（UNION、UNION ALL、MINUS、INTERSECT）、聚集函式（SUM、COUNT、GROUP BY）不會被展開。
 
半連線關注重點在於：對於外表某個記錄，在內表中找到一個匹配記錄就返回外表記錄。
 
不展開查詢：
類似NEST LOOP方式，對主查詢每條記錄都執行一次子查詢，在執行計劃中叫做FILTER。
ORACLE 10G中，使用提示。（這個是非半連線）
SQL>SELECT ID
FROM A
WHERE EXISTS ( SELECT 1 FROM B WHERE A.ID=B.P_ID);
 
展開子查詢：
SQL>SELECT ID
FROM A
WHERE EXISTS ( SELECT 1 FROM B WHERE A.ID=B.P_ID);
此時執行計劃中，會看到HASH JOIN SEMI字樣，說明這是一個半連線。
好處是：
對於A表中一條記錄，發現B中匹配一條就停止掃描B，轉而處理A的下一條記錄。
返回結果無需去重，即使A和B記錄時1：n，表A每個記錄只會返回一次。
 
從ORACLE 9i開始IN和EXISTS已經沒有區別了，執行計劃是一樣的。
 
SEMI JOIN的HINT如下：
EXISTS：
SQL>SELECT ID
FROM A
WHERE EXISTS (SELECT 1 FROM B WHERE A.ID=B.P_ID);
 
IN：
SQL>SELECT ID
FROM A
WHERE IN (SELECT 1 FROM B WHERE A.ID=B.P_ID);
 
NOT EXISTS：預設就使用展開的ANTI-JOIN
SQL>SELECT ID
FROM A
WHERE NOT EXISTS (SELECT 1 FROM B WHERE A.ID=B.P_ID);
 
NOT IN：
與NOT EXISTS的區別在於處理NULL
NOT IN檢視子結果中有沒有NULL，如果有NULL，返回FALSE；NOT EXISTS不關心有沒有NULL，只關心記錄數，如果有記錄，返回FALSE。
NOT IN可能在匹配列上，引起效能問題，原因是索引失效。
 
HINT：
操作

操作

Nest Loop

Hash Join

Sort Mereg

Join

USE_NL

USE_HASH

USE_MERGE

Anti Join

NL_AJ

HASH_AJ

MERGE_AJ

Semi

NL_SJ

HASH_SJ

MERGE_SJ


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

相關文章