關于表聯結方法_sort-merge join

在比較經典的表聯結方法中，nested loop join和hash join是比較常用的，對于sort-merge join來說，可能略微有些陌生。

在資料庫中有一個隐含參數，預設是開啟的。

NAME                                                                             VALUE                          ISDEFAULT ISMOD      ISADJ

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

_optimizer_sortmerge_join_enabled                                                TRUE                           TRUE      FALSE      FALSE

因為這種聯結方式如果使用不當回消耗大量的系統資源，在一些生産系統中都選擇手動禁用這種聯結。

這種聯結的運作原理想比nested loop join,hash join而言沒有驅動表的說法，是以sort-merge join會可能産生大量的随機讀。

比如我們有表emp,dept,

查詢語句為

select  empno,ename,dname,loc from emp,dept where emp.deptno =dept.deptno

如果采用sort-merge join的時候，就會對emp,dept表進行order by 的操作。

類似下面兩個操作

      select empno,ename ,deptno from emp order by deptno;

      select deptno,dname,loc from dept order by deptno;

因為排序後的資料都是有序的，然後對兩個子結果集根據deptno進行比對。

然後選擇兩端的資料列，根據列的要求篩選資料。

我們先來看一個使用sort-merge join的執行計劃，實際中可能需要用到sort-merge join的場景就是 類似

tab1.column1 between tab2.column2 and tab2.column3 這種形式的查詢中。

我們可以使用hint ordered或者 use_merge來引導查詢走sort-merge join ,簡單模拟一下。

使用hint ordered

SQL> select /*+ordered*/  empno,ename,dname,loc from emp,dept where emp.deptno between dept.deptno-10 and dept.deptno+10

  2  /

39 rows selected.

Execution Plan

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

Plan hash value: 667632632

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

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

|   0 | SELECT STATEMENT     |      |    39 |  1287 |     6  (34)| 00:00:01 |

|   1 |  MERGE JOIN          |      |    39 |  1287 |     6  (34)| 00:00:01 |

|   2 |   SORT JOIN          |      |    14 |   182 |     3  (34)| 00:00:01 |

|   3 |    TABLE ACCESS FULL | EMP  |    14 |   182 |     2   (0)| 00:00:01 |

|*  4 |   FILTER             |      |       |       |            |          |

|*  5 |    SORT JOIN         |      |     4 |    80 |     3  (34)| 00:00:01 |

|   6 |     TABLE ACCESS FULL| DEPT |     4 |    80 |     2   (0)| 00:00:01 |

Predicate Information (identified by operation id):

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

   4 - filter("EMP"."DEPTNO")

   5 - access(INTERNAL_FUNCTION("EMP"."DEPTNO")>="DEPT"."DEPTNO"-10)

       filter(INTERNAL_FUNCTION("EMP"."DEPTNO")>="DEPT"."DEPTNO"-10)

Statistics

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       2210  bytes sent via SQL*Net to client

        541  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

         39  rows processed

可以看到對emp,dept都做了全表掃描，對資料進行了排序，然後對根據deptno對結果集進行了比對和關聯，最後把結果集輸出。

也可以使用hint use_merge來實作一樣的效果。

SQL> select /*+use_merge(dept,emp)*/ empno,ename,dname,loc from emp,dept where emp.deptno between dept.deptno-10 and dept.deptno+10

Plan hash value: 1726864587

|   2 |   SORT JOIN          |      |     4 |    80 |     3  (34)| 00:00:01 |

|   3 |    TABLE ACCESS FULL | DEPT |     4 |    80 |     2   (0)| 00:00:01 |

|*  5 |    SORT JOIN         |      |    14 |   182 |     3  (34)| 00:00:01 |

|   6 |     TABLE ACCESS FULL| EMP  |    14 |   182 |     2   (0)| 00:00:01 |

   4 - filter("EMP"."DEPTNO"

   5 - access("EMP"."DEPTNO">="DEPT"."DEPTNO"-10)

       filter("EMP"."DEPTNO">="DEPT"."DEPTNO"-10)

       1796  bytes sent via SQL*Net to client

合并排序的思路和資料結構中的合并排序算法，在資料篩選條件有限或者傳回有限資料行的查詢比較合适。如果本身表中的資料量很大，做sort-merge join就會耗費大量的cpu資源，臨時表空間，相比來說不是很劃算，完全可以通過其他的聯結方式來實作。