Oracle 表三種串連方式(sql最佳化）

標籤：

轉自網路

1. NESTED LOOP

對於被串連的資料子集較小的情況，nested loop串連是個較好的選擇。nested loop就是掃描一個表，每讀到一條記錄，就依據索引去還有一個表裡面尋找，沒有索引一般就不會是 nested loops。一般在nested loop中， 驅動表滿足條件結果集不大，被驅動表的串連欄位要有索引。這樣就走nstedloop。假設驅動表返回記錄太多，就不適合nested loops了。假設串連欄位沒有索引。則適合走hash join，由於不須要索引。

可用ordered提示來改變CBO預設的驅動表，可用USE_NL(table_name1 table_name2)提示來強制使用nested loop。

要點例如以下：
  1）對於被串連的資料子集較小的情況，嵌套迴圈串連是個較好的選擇
  2）使用USE_NL(table_name1 table_name2)但是強制CBO 運行嵌套迴圈串連
  3）Nested loop一般用在串連的表中有索引。而且索引選擇性較好的時候
  4）OIN的順序非常重要。驅動表的記錄集一定要小，返回結果集的回應時間是最快的。
  5）Nested loops 工作方式是從一張表中讀取資料，訪問還有一張表（一般是索引）來做匹配。nested loops適用的場合是當一個關聯表比較小的時候，效率會更高。

2. HASH JOIN

hash join是CBO 做大資料集串連時的經常使用方式。

最佳化器掃描小表（資料來源），利用串連鍵（也就是依據串連欄位計算hash 值）在記憶體中建立hash表，然後掃描大表。每讀到一條記錄就探測hash表一次，找出與hash表匹配的行。

當小表能夠所有放入記憶體中，其成本接近全表掃描兩個表的成本之和。假設表非常大不能全然放入記憶體，這時最佳化器會將它切割成若干不同的分區，不能放入記憶體的部分就把該分區寫入磁碟的暫時段，此時要有較大的暫時段從而盡量提高I/O 的效能。暫時段中的分區都須要換進記憶體做hash join。這時候成本接近於全表掃描小表+分區數*全表掃描大表的代價和。

至於兩個表都進行分區。其優點是能夠使用parallel query，就是多個進程同一時候對不同的分區進行join，然後再合并。

可是複雜。

使用hash join時，HASH_AREA_SIZE初始化參數必須足夠的大，假設是9i，Oracle建議使用SQL工作區自己主動管理，設定WORKAREA_SIZE_POLICY 為AUTO，然後調整PGA_AGGREGATE_TARGET就可以。

下麵條件下hash join可能有優勢：
   1）兩個巨大的表之間的串連。

   2）在一個巨大的表和一個小表之間的串連。

要點例如以下：
   1）散列串連是CBO 做大資料集串連時的經常使用方式.
   2）也能夠用USE_HASH(table_name1 table_name2)提示來強制使用散列串連
   3）Hash join在兩個表的資料量區別非常大的時候.
   4）Hash join的工作方式是將一個表（一般是小一點的那個表）做hash運算並儲存到hash列表中。從還有一個表中抽取記錄，做hash運算。到hash 列表中找到對應的值。做匹配。

可用ordered提示來改變CBO預設的驅動表。可用USE_HASH(table_name1 table_name2)提示來強制使用hash join。

3. SORT MERGE JOIN

 a）對串連的每一個表做table access full；
 b）對table access full的結果進行排序；
 c）進行merge join對排序結果進行合并。

sort merge join效能開銷差點兒都在前兩步。

通常是在沒有索引的情況下，9i開始已經非常少出現。由於其排序成本高，大多為hash join替代。 
通常情況下hash join的效果都比sort merge join要好。可是，假設行源已經被排過序。在運行sort merge join時不須要再排序。這時sort merge join的效能會優於hash join。
當全表掃描比“索引範圍掃描後再通過rowid進行表訪問”更可取的情況下，sort merge join會比nested loops效能更佳。

要點例如以下：
  1）使用USE_MERGE(table_name1 table_name2)來強制使用排序合并串連.
  2）Sort Merge join 用在沒有索引，而且資料已經排序的情況.
  3）串連步驟：將兩個表排序，然後將兩個表合并。
  4）通常情況下，僅僅有在下面情況發生時。才會使用此種JOIN方式：
     a）RBO模式
     b）不等價關聯(>,<,>=,<=,<>)
     c）bHASH_JOIN_ENABLED=false
     d）資料來源已排序
     e）Merge Join 是先將關聯表的關聯列各自做排序，然後從各自的排序表中抽取資料，到還有一個排序表中做匹配，由於merge join須要做很多其它的排序。所以消耗的資源很多其它。

 

     f) like ,not like 
        通常來講，可以使用merge join的地方。hash join都可以發揮更好的效能

可用USE_MERGE(table_name1 table_name2)提示強制使用sort merge join。

Oracle 表三種串連方式(sql最佳化）