Oracle的三種表連線方式

在做表join的時候， Oracle有三種方式， 與其說有三種方式， 不如說是三種策略。

分別是：

• sort merge join(SMJ)
• nest loop(NL)
• hash join(HJ)

根據我的理解來講講這三種策略。

首先講講Row Source, 根據Oracle官方的解釋，

Row source is a row set returned by a step in the execution plan along with a control structure that can iteratively process the rows. The row source can be a table, a view or result of a join or grouping operation.

總之， Row source是oracle根據執行計劃生成的可運算元據集, 這種說法比Table更為嚴謹，因為join的兩側可能是view或者其他的object.

1) sort merge join.

例如:

select * from t1 inner join t2 on t1.id=t2.id

首先會把row source1(t1)先載入記憶體， 進行排序， 然後把row source2(t2)載入記憶體，進行排序，然後進行merge操作。

什麼是merge 操作？就是將兩邊的行按照連線條件連起來， (t1.id=t2.id)。

不難看出，這種方式將需要連線的兩張表中的列都放到記憶體中，然後進行排序，而排序是一個消耗資源的操作，這樣對於兩張比較大的表，效能恐怕會比較差。

所以， 這種策略適合於表比較小， 或者在連線列上有索引的表。因為索引列已經排過序了。

 

2) Nested loops

選定一張表做為驅動表，Oracle會遍歷驅動表中的每一行，根據連線條件去匹配第二張表中的行。

比如第一張表中有50行資料， 第二張表中有100行資料， 這樣遍歷的時間約等於50*100+50*磁頭切換時間

如果選擇第二張表作為驅動表，遍歷時間約等於100*50+100*磁頭切換時間。

可見使用小表作為驅動表可以減少I/O，效能會比較好。

 

例如:

select from t1 inner join t2 on t1.id=t2.id

以上的這個hint 代表的是按照sql中指定的表順序進行連線。也就是我把t1作為驅動表(Driving table).

Orace會根據t1中的每一行， 去尋找t2中滿足t1.id=t2.id的行，然後返回到結果集。

不難看出， 如果在內部表的查詢列上有索引的話， 查詢的效率將提升。

據說，對於可並行執行的大表， 使用分割槽了的大表作為Driving table，效能會比較好，因為會在每個分割槽上並行執行，但是取決於硬體是否支援多個磁碟，多個CPU並行執行，這個我並沒有試過，所以不敢妄加定論。

 

3) Hash Join

僅針對CBO有效。

使用較小的Row source 作為Hash table和Bitmap. 而第二個row source被hashed，根據bitmap與第一個row source生成的hash table 相匹配，bitmap查詢的速度極快。

例如:

select   * from t1 inner join t2 on t1.id=t2.id

特別的，當Hash Table很大而不能全部留存在記憶體中的時候，這種Join策略更為實用。

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