Oracle表連線操作——Merge Sort Join（合併排序連線）

 

關係型資料庫並不是最早出現的資料庫表現形式，之前還存在層次、網狀資料庫結構。隨著關係型資料庫的出現，以資料表的方式進行資訊留存的方案迅速發展起來。關係型資料庫的重要元素包括資料表和表連線，藉助各種型別的表連線，可以將平鋪直敘的資訊加以組裝拼接。

 

 

1、Merge Sort Join原理機制

 

Nest Loop Join巢狀迴圈是一種比較古老的連線匹配方式，特點是透過兩層的迴圈結構，將符合條件的資料行整理出來。巢狀迴圈的最大缺陷之一，就是伴隨著驅動表被驅動表之間的選擇，以及大量隨機讀現象。

 

 

Merge Sort Join連線的優勢就是可以一定程度上減少隨機讀的情況。合併排序連線的最大特徵是在一次掃描的同時，就判斷連線。不會像Nest Loop Join那樣頻繁的進行資料讀取。使用這種方式的前提，就是連線的兩個資料集合必須按照連線列的順序進行排序。具體操作流程如下：

 

ü        對Merge Sort Join連線而言，不存在驅動表和被驅動表的問題。兩邊的資料集合沒有順序區別，都要進行排序操作；

ü        根據Oracle排序規則和方法，按照連線列的順序對兩個資料集合進行排序；

ü        依次對兩邊的資料集合進行掃描，由於已經是排序過得結果，可以直接確定連線條件是否匹配；

ü        確定進行連線的兩端資料行，再依據篩選列的要求獲取資料；

 

下面是一個進行Merge Sort Join的執行計劃：

 

//使用Merge Sort Join方法

SQL> select /*+use_merge(segs,tabs)*/* from segs, tabs where segs.segment_name=tabs.table_name;

已選擇865行。

 

執行計劃

----------------------------------------------------------

Plan hash value: 3475644097

 

------------------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |   990 |   354K|       |   144   (2)| 00:00:02 |

|   1 |  MERGE JOIN         |      |   990 |   354K|       |   144   (2)| 00:00:02 |

|   2 |   SORT JOIN         |      |   968 |   229K|   712K|    65   (2)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| TABS |   968 |   229K|       |    11   (0)| 00:00:01 |

|*  4 |   SORT JOIN         |      |  2267 |   274K|   824K|    79   (2)| 00:00:01 |

|   5 |    TABLE ACCESS FULL| SEGS |  2267 |   274K|       |    13   (0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

       filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

統計資訊

----------------------------------------------------------

       2010  recursive calls

          0  db block gets

        378  consistent gets

          0  physical reads

          0  redo size

      72346  bytes sent via SQL*Net to client

       1003  bytes received via SQL*Net from client

         59  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

        865  rows processed

 

//使用巢狀迴圈；

SQL> select /*+use_nl(segs,tabs)*/* from segs, tabs where segs.segment_name=tabs.table_name;

已選擇865行。

執行計劃

----------------------------------------------------------

Plan hash value: 840690564

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |   990 |   354K| 11075   (1)| 00:02:13 |

|   1 |  NESTED LOOPS      |      |   990 |   354K| 11075   (1)| 00:02:13 |

|   2 |   TABLE ACCESS FULL| TABS |   968 |   229K|    11   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| SEGS |     1 |   124 |    11   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

統計資訊

----------------------------------------------------------

       1930  recursive calls

          0  db block gets

      43978  consistent gets

          0  physical reads

          0  redo size

      70556  bytes sent via SQL*Net to client

       1003  bytes received via SQL*Net from client

         59  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

        865  rows processed

 

 

上面程式碼示例中給出了兩個執行計劃，給我們如下的資訊。

 

首先，我們觀察使用use_merge提示的SQL，在Hint的作用下，CBO生成的執行計劃中使用Merge Sort Join連線方式。在執行計劃中Oracle對兩個資料表進行Sort操作，之後對排序過的結果進行Merge連線。其中Oracle對兩個資料表進行的都是全表掃描操作。

 

 

另一個執行計劃是使用use_nl控制的Nest Loop Join連線方式。中間同樣也是沒有使用索引等方式。其中，產生了大量邏輯讀。見下表對比：

 

 

透過資料資訊的對比，我們可以明顯的看出兩個相同結果集合的SQL，由於不同的連線方式而帶來的差異。Merge Sort Join可以大大消除由於Nest Loop Join帶來的隨機讀過多的情況。而由於進行的排序操作，Merge Sort Join也要付出相應的排序空間損耗。

 

2、Merge Sort Join與排序空間

 

對Oracle熟悉的朋友們通常對Sort和Group操作是比較敏感的。Sort和Group by都是需要單獨對資料集進行的操作，要消耗額外的CPU和記憶體資源。CPU資源主要消耗在演算法排序和結果集合整合上。而記憶體資源的消耗更加需要關注，排序操作要在專門的PGA排序區內完成。如果PGA中特定的排序大小（pga_aggregat_target:sort_area_size）不足以進行排序操作，也就是說需要排序分組的資料集合特別大的時候，Oracle需要呼叫Temp表空間的容量來進行操作。

 

 

這也就是問題的所在。Temp表空間資料儲存位於磁碟中，速度與記憶體相差很多。所以，當進行排序操作的資料集合很大，會出現效能急劇的下降可能。在實際業務場景中，對海量資料集合的處理、Data Warehouse應用的操作，都可能出現這種情況。

 

回到Merge Sort Join來，就可以理解這種連線方式的缺陷之處了。要進行Merge Sort Join，其中的Sort過程不可避免。而使用Sort操作帶來的優勢就是不需要進行過多的隨機讀。在資料集合量很大的時候，Merge Sort Join的效率可能會很差。

 

 

3、對索引路徑的借用

 

在Nest Loop Join中，對連線列進行索引處理，可以很大程度上提升執行計劃效率，減少隨機讀的數量。道理就是借用了索引排序這個現實。而Merge Sort Join對索引的應用效果遠不如Nest Loop Join。

 

索引環境構建：

 

//索引構建

SQL> create index idx_tabs_name on tabs(table_name);

Index created

 

SQL> create index idx_segs_name on segs(segment_name);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'SEGS',cascade => true);

PL/SQL procedure successfully completed

 

SQL> exec dbms_stats.gather_table_stats(user,'TABS',cascade => true);

PL/SQL procedure successfully completed

 

 

執行計劃如下：

 

 

SQL> explain plan for select /*+use_merge(tabs,segs)*/* from segs,tabs where segs.segment_name=tabs.table_name;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3475644097

--------------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |   990 |   354K|       |   144   (2)| 00:00:

|   1 |  MERGE JOIN         |      |   990 |   354K|       |   144   (2)| 00:00:

|   2 |   SORT JOIN         |      |   968 |   229K|   712K|    65   (2)| 00:00:

|   3 |    TABLE ACCESS FULL| TABS |   968 |   229K|       |    11   (0)| 00:00:

|*  4 |   SORT JOIN         |      |  2267 |   274K|   824K|    79   (2)| 00:00:

|   5 |    TABLE ACCESS FULL| SEGS |  2267 |   274K|       |    13   (0)| 00:00:

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

       filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

18 rows selected

 

 

由於Merge Sort Join本身就帶有排序的特性，而且返回的結果集合中包括所有欄位。所以通常的執行計劃中，即使連線列存在索引，也不會進入到執行計劃中。除非進行一些特定列處理。

 

 

SQL> explain plan for select /*+use_merge(tabs,segs)*/segs.segment_name,tabs.table_name from segs,tabs where segs.segment_name=tabs.table_name;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 712326860

--------------------------------------------------------------------------------

| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Ti

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |               |   990 | 37620 |     9  (23)| 00

|   1 |  MERGE JOIN            |               |   990 | 37620 |     9  (23)| 00

|   2 |   SORT JOIN            |               |   968 | 17424 |     4  (25)| 00

|   3 |    INDEX FAST FULL SCAN| IDX_TABS_NAME |   968 | 17424 |     3   (0)| 00

|*  4 |   SORT JOIN            |               |  2267 | 45340 |     5  (20)| 00

|   5 |    INDEX FAST FULL SCAN| IDX_SEGS_NAME |  2267 | 45340 |     4   (0)| 00

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

       filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

18 rows selected

 

 

在對返回結果進行處理的情況下，索引路徑會出現的。

 

 

4、結論

 

Merge Sort Join是一種古老經典的排序模型，類似於資料結構時代的合併排序演算法。Merge Sort Join引入的最大優勢是避免同Nest Loop Join類似的大量隨機讀現象，但是同時也引入了Sort空間變化的問題。

 

 

隨著海量資料處理場景的增多，Merge Sort Join暴露出缺陷的機會越來越多。而Nest Loop Join的大量隨機讀問題，也是可以透過索引等常規手段加以最佳化。