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:0

oracle表连接-sortmergejoins排序合并连接(一)