程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> oracle表連接-sortmergejoins排序合並連接

oracle表連接-sortmergejoins排序合並連接

編輯：Oracle教程

oracle表連接-sortmergejoins排序合並連接

一. sort merge joins連接(排序合並連接) 原理

指的是兩個表連接時, 通過連接列先分別排序後, 再通過合並操作來得到最後返回的結果集的方法.

假如表 T1 和 T2 的連接方式是排序合並連接, oracle 執行步驟如下:
(1) 根據 sql 語句中的謂詞條件(如果有) 訪問 T1 表, 得到一個過濾的結果集, 然後按照 T1 中的連接列對結果集進行排序
(2) 根據 sql 語句中的謂詞條件(如果有) 訪問 T2 表, 得到一個過濾的結果集, 然後按照 T2 中的連接列對結果集進行排序
(3) 將 1 和 2 的結果集合並起來, 對記錄進行匹配得到最後的結果集.

通常來說, sort merge joins連接(排序合並連接) 使用並不廣泛, 因為在大部分情況下使用 nested loops 或者 hash joins 都能獲得比它更好的執行效率, 但是由於 hash joins 只能用於等值連接條件, 所以在非等值條件連接以及非 like 非 "<>" 情況下, 如果連接列上已經有排序, 使用 sort merge joins連接方式能獲得比較好的執行效率
二. sort merge joins連接(排序合並連接) 特性

(1) 驅動表最多訪問一次, 如果獨立的謂詞條件(不涉及驅動表字段的函數或者表達式等)不成立, 則不用再去訪問驅動表
(2) 被驅動表最多訪問一次. 如果驅動表沒有記錄, 被驅動表不用訪問
(3) 驅動表的選擇對於執行成本以及性能沒有太大的影響
(4) 支持大部分的連接條件, 比如 ">" "<" ">=" "<=", 不支持 like, "<>"

構造試驗數據

SQL> CREATE TABLE t1 (
  2    id NUMBER NOT NULL,
  3    n NUMBER,
  4    pad VARCHAR2(4000),
  5    CONSTRAINT t1_pk PRIMARY KEY(id)
  6  );

Table created.

SQL> CREATE TABLE t2 (
  2    id NUMBER NOT NULL,
  3    t1_id NUMBER NOT NULL,
  4    n NUMBER,
  5    pad VARCHAR2(4000),
  6    CONSTRAINT t2_pk PRIMARY KEY(id),
  7    CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1
  8  );

Table created.

SQL> CREATE TABLE t3 (
  2    id NUMBER NOT NULL,
  3    t2_id NUMBER NOT NULL,
  4    n NUMBER,
  5    pad VARCHAR2(4000),
  6    CONSTRAINT t3_pk PRIMARY KEY(id),
  7    CONSTRAINT t3_t2_fk FOREIGN KEY (t2_id) REFERENCES t2
  8  );

Table created.
SQL> CREATE TABLE t4 (
  2    id NUMBER NOT NULL,
  3    t3_id NUMBER NOT NULL,
  4    n NUMBER,
  5    pad VARCHAR2(4000),
  6    CONSTRAINT t4_pk PRIMARY KEY(id),
  7    CONSTRAINT t4_t3_fk FOREIGN KEY (t3_id) REFERENCES t3
  8  );


Table created.

SQL> execute dbms_random.seed(0)

PL/SQL procedure successfully completed.


SQL> INSERT INTO t1 SELECT rownum, rownum, dbms_random.string('a',50) FROM dual CONNECT BY level <= 10 ORDER BY dbms_random.random;

10 rows created.

SQL> INSERT INTO t2 SELECT 100+rownum, t1.id, 100+rownum, t1.pad FROM t1, t1 dummy ORDER BY dbms_random.random;

100 rows created.

SQL> INSERT INTO t3 SELECT 1000+rownum, t2.id, 1000+rownum, t2.pad FROM t2, t1 dummy ORDER BY dbms_random.random;

1000 rows created.

SQL> INSERT INTO t4 SELECT 10000+rownum, t3.id, 10000+rownum, t3.pad FROM t3, t1 dummy ORDER BY dbms_random.random;

10000 rows created.

SQL> COMMIT;

Commit complete.

使用 hint 讓執行計劃以 T3 作為驅動表

SQL> select /*+ leading(t3) use_merge(t4) */ *
  2  from t3, t4
  3  where t3.id = t4.t3_id and t3.n = 1100;


10 rows selected.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------


SQL_ID  g0rdyg9hdh9m0, child number 0
-------------------------------------
select /*+ leading(t3) use_merge(t4) */ * from t3, t4 where t3.id =
t4.t3_id and t3.n = 1100


Plan hash value: 3831111046


-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     10 |00:00:00.02 |     119 |       |       |          |
|   1 |  MERGE JOIN         |      |      1 |     10 |     10 |00:00:00.02 |     119 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |      1 |      1 |00:00:00.01 |      15 |  2048 |  2048 | 2048  (0)|
|*  3 |    TABLE ACCESS FULL| T3   |      1 |      1 |      1 |00:00:00.01 |      15 |       |       |          |
|*  4 |   SORT JOIN         |      |      1 |  10000 |     10 |00:00:00.02 |     104 |   974K|   535K|  865K (0)|
|   5 |    TABLE ACCESS FULL| T4   |      1 |  10000 |  10000 |00:00:00.01 |     104 |       |       |          |
-----------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - filter("T3"."N"=1100)
   4 - access("T3"."ID"="T4"."T3_ID")
       filter("T3"."ID"="T4"."T3_ID")

使用 hint 讓執行計劃以 T4 作為驅動表

SQL> select /*+ leading(t4) use_merge(t3) */ *
  2  from t3, t4
  3  where t3.id = t4.t3_id and t3.n = 1100;


10 rows selected.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------


SQL_ID  gxuwn06y1c1az, child number 0
-------------------------------------
select /*+ leading(t4) use_merge(t3) */ * from t3, t4 where t3.id =
t4.t3_id and t3.n = 1100


Plan hash value: 875334572


-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     10 |00:00:00.04 |     119 |       |       |          |
|   1 |  MERGE JOIN         |      |      1 |     10 |     10 |00:00:00.04 |     119 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |  10000 |   1001 |00:00:00.04 |     104 |   974K|   535K|  865K (0)|
|   3 |    TABLE ACCESS FULL| T4   |      1 |  10000 |  10000 |00:00:00.01 |     104 |       |       |          |
|*  4 |   SORT JOIN         |      |   1001 |      1 |     10 |00:00:00.01 |      15 |  2048 |  2048 | 2048  (0)|
|*  5 |    TABLE ACCESS FULL| T3   |      1 |      1 |      1 |00:00:00.01 |      15 |       |       |          |
-----------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   4 - access("T3"."ID"="T4"."T3_ID")
       filter("T3"."ID"="T4"."T3_ID")
   5 - filter("T3"."N"=1100)

從返回的執行計劃結果中我們可以看到:
1. 以 T3 為驅動表和以 T4 為驅動表, 兩者的 cost (A-Time) 和 buffers 都差不多

2. 以 T3 為驅動表時, T3 訪問一次, T4 也是訪問一次; 以 T4 為驅動表時, T4 訪問一次, T3 也是訪問一次

3. 需要排序, 如果 PGA 空間重足時在 PGA 中排序, 不如果不足則交換到磁盤上排序

另外, 在執行計劃中有幾個統計信息列 0Mem, 1Mem, Use_Mem 需要介紹一下
0Mem 指的是預計在 PGA 中排序需要的內存大小1Mem 指的是當內存大小（PGA）不足以進行排序, 預計將數據一次交換到磁盤空間的內存大小Used-Mem 指的是執行時實際使用的內存大小, 其中括號中的數字代表進行磁盤交換的次數, 0 代表沒有進行磁盤交換
三. sort merge joins連接(排序合並連接) 優化

SQL> select /*+ leading(t3) use_merge(t4) */ *
  2  from t3, t4
  3  where t3.id = t4.t3_id and t3.n = 1100 and t4.n = 10034;


SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------


SQL_ID  bg9h60c7ak3ud, child number 0
-------------------------------------
select /*+ leading(t3) use_merge(t4) */ * from t3, t4 where t3.id =
t4.t3_id and t3.n = 1100 and t4.n = 10034


Plan hash value: 3831111046


-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.01 |     119 |       |       |          |
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:00.01 |     119 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |      1 |      1 |00:00:00.01 |      15 |  2048 |  2048 | 2048  (0)|
|*  3 |    TABLE ACCESS FULL| T3   |      1 |      1 |      1 |00:00:00.01 |      15 |       |       |          |
|*  4 |   SORT JOIN         |      |      1 |      1 |      1 |00:00:00.01 |     104 |  2048 |  2048 | 2048  (0)|
|*  5 |    TABLE ACCESS FULL| T4   |      1 |      1 |      1 |00:00:00.01 |     104 |       |       |          |
-----------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - filter("T3"."N"=1100)
   4 - access("T3"."ID"="T4"."T3_ID")
       filter("T3"."ID"="T4"."T3_ID")
   5 - filter("T4"."N"=10034)


SQL> create index t4_n on t4(n);


Index created.


SQL> select /*+ leading(t3) use_merge(t4) */ *
  2  from t3, t4
  3  where t3.id = t4.t3_id and t3.n = 1100 and t4.n = 10034;


SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------


SQL_ID  bg9h60c7ak3ud, child number 0
-------------------------------------
select /*+ leading(t3) use_merge(t4) */ * from t3, t4 where t3.id =
t4.t3_id and t3.n = 1100 and t4.n = 10034


Plan hash value: 1501658231


------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      1 |00:00:00.01 |      18 |      1 |       |       |          |
|   1 |  MERGE JOIN                   |      |      1 |      1 |      1 |00:00:00.01 |      18 |      1 |       |       |          |
|   2 |   SORT JOIN                   |      |      1 |      1 |      1 |00:00:00.01 |      15 |      0 |  2048 |  2048 | 2048  (0)|
|*  3 |    TABLE ACCESS FULL          | T3   |      1 |      1 |      1 |00:00:00.01 |      15 |      0 |       |       |          |
|*  4 |   SORT JOIN                   |      |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS BY INDEX ROWID| T4   |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |       |       |          |
|*  6 |     INDEX RANGE SCAN          | T4_N |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - filter("T3"."N"=1100)
   4 - access("T3"."ID"="T4"."T3_ID")
       filter("T3"."ID"="T4"."T3_ID")
   6 - access("T4"."N"=10034)


SQL> create index t3_n on t3(n);


Index created.


SQL> select /*+ leading(t3) use_merge(t4) */ *
  2  from t3, t4
  3  where t3.id = t4.t3_id and t3.n = 1100 and t4.n = 10034;


SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------


SQL_ID  bg9h60c7ak3ud, child number 0
-------------------------------------
select /*+ leading(t3) use_merge(t4) */ * from t3, t4 where t3.id =
t4.t3_id and t3.n = 1100 and t4.n = 10034


Plan hash value: 1827980052


------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      1 |00:00:00.01 |       6 |      1 |       |       |          |
|   1 |  MERGE JOIN                   |      |      1 |      1 |      1 |00:00:00.01 |       6 |      1 |       |       |          |
|   2 |   SORT JOIN                   |      |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| T3   |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | T3_N |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |       |       |          |
|*  5 |   SORT JOIN                   |      |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |  2048 |  2048 | 2048  (0)|
|   6 |    TABLE ACCESS BY INDEX ROWID| T4   |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |       |       |          |
|*  7 |     INDEX RANGE SCAN          | T4_N |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   4 - access("T3"."N"=1100)
   5 - access("T3"."ID"="T4"."T3_ID")
       filter("T3"."ID"="T4"."T3_ID")
   7 - access("T4"."N"=10034)

從上面的執行計劃中可以看出, 全表掃描後最後使用的 buffer 為 119, 在一個表上建立索引使用索引范圍掃描後 buffer 為 18, 在兩個表上建立的索引使用索引范圍掃描後 buffer 為 6. 由此可以見, 在表的謂詞條件上如果有索引的話, 將會提高執行效率.

此外, 由於 sort merge joins 需要先在 PGA 中進行排序,, 如果 PGA 空間不足, 就會將數據交換到磁盤上進行排序。由於, 磁盤相對於內存來說是慢速設備，因此在磁盤上排序會比在內存上排序慢, 另外排序排序消耗的時間還需要加上數據在內存和磁盤上傳輸的時間，因此盡可能減少磁盤排序的次數也就會提高執行效率, 有兩種方法會減少磁盤排序：

1. 增大 PGA 的大小, 如果是 oracle 10g，需要增加參數 pga_aggregate_target 的大小，如果是 oracle 11g，則增加 memory_target 的大小

2. 減少排序的數據量, 一些不需要的字段就不要寫在 select 後面

四. 小結

遇到 sql 調優時，如果執行計劃顯示表的連接方式是 sort merge join：

首先，看看 sql 語句是不是表的連接方式有沒有可能轉換為 hash join(等值連接條件)

其次，只能使用 sort merge join 時看看表的謂詞條件上是不是有索引

最後，看看執行計劃排序占用的內存大小是不是在磁盤上有排序, 是不是能夠避免在磁盤上排序