Oracle 連接配接和半連接配接

連接配接

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);

展開子查詢：

此時執行計劃中，會看到HASH JOIN SEMI字樣，說明這是一個半連接配接。

好處是：

對于A表中一條記錄，發現B中比對一條就停止掃描B，轉而處理A的下一條記錄。

傳回結果無需去重，即使A和B記錄時1：n，表A每個記錄隻會傳回一次。

從ORACLE 9i開始IN和EXISTS已經沒有差別了，執行計劃是一樣的。

SEMI JOIN的HINT如下：

EXISTS：

WHERE EXISTS (SELECT 1 FROM B WHERE A.ID=B.P_ID);

IN：

WHERE IN (SELECT 1 FROM B WHERE A.ID=B.P_ID);

NOT EXISTS：預設就使用展開的ANTI-JOIN

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_HAS	USE_MERGE
Anti Join	NL_AJ	HASH_AJ	MERGE_AJ
Semi	NL_SJ