Oracle union vs union all 用法區別

ifsapp

一．基礎知識：

1．  Union 排序，Union All 不排序。

Union中兩個select 語句查詢欄位類型要匹配，而且欄位個數要相同。

Union在進行表連接以後會對結果進行篩選，刪除重複記錄，對結果進行排序，將排序後的結果返回。   具有distinct 的功能。

Union All 卻僅僅是將兩個select 語句查詢結果合併後返回。可能出現重複記錄。

所以union all 效率要比union高。在能夠確定沒有重複記錄的情況下儘量使用union all。

如果表有多個索引列的時候，用union 替換 where 中的or 效率會提高不少。索引列使用or會造成全資料表掃描。如果有column 沒有使用索引，就得記得or了。

2．  索引：

避免將索引建立在任何可能為空的列上邊。Oracle將無法使用該索引。

避免在索引列上使用 is null  、  is not null。

當索引建立在多個列的時候，只用當索引的第一列被where子句引用時，優化器才會使用該索引，僅使用第二列的時候，優化器就會進行全資料表掃描而忽略該索引。

3．  排序：

Union對兩個結果集進行並集操作，不包括重複行，同時進行預設規則排序;

UnionAll 對兩個結果集進行並集操作，包括重複行，不進行排序；

Intersect對兩個結果集進行交集操作，不包括重複行，同時進行預設規則排序；

Minus對兩個結果集進行差操作，不包括重複行，同時進行預設規則排序。

可以在最後一個結果集中制定Order By 子句改變排序方式。

4．  Oracle 中的in 和 exists

Select* from t1 where x in (select y from t2)

事實上可以理解為：

Select* from t1, (select distinct y from t2) t2 where t1.x = t2.y

從這句很自然的可以想到t2絕對不能是個大表，因為需要對t2進行全表的“唯一排序”，如果t2很大這麼排序的性能是不可忍受的。但是t1可以很大，為什麼呢？最通俗的理解就是因為t1.x = t2.y可以走索引。但這並不是一個很好的解釋。試想如果t1.x 和 t2.y都有索引，我們知道索引是種有序的結構，因此t1和t2之間最佳的方案是走merge join。另外，如果t2.y上有索引，對t2的排序性能也是很大提高。

Select* from t1 where exists ( select null from t2 where y = x )

可以理解為：

Forx in (select * from t1)

Loop

If (exists(select null from t2 where y = x.x)) then

OUTPUTTHE RECODE！

Endif

    End loop

這個更容易理解，t1永遠是個資料表掃描，因此t1絕對不能是個大表，而t2可以很大，因為y=x.x

可以走t2.y的索引。IN適合於外表大而內表小，exists合適外表小而內表大的情況。