Merge Sort Join连接的优势就是可以一定程度上减少随机读的情况。就是逻辑读少,但是sort 排序多.
hash join 逻辑读多，但是sort 排序少.
hash join 是 Merge Sort Join的一个升级补充


1.建立测试表


SQL> create table system.t1(id number(10),name varchar(10));


Table created.


SQL> begin
  2      for i in 1 .. 10000 loop      
  3               insert into system.t1(id,name) select trunc(dbms_random.value(0,10000)) ,'t1' from dual;
  4               commit;
  5      end loop;
  6  end;
  7  /




PL/SQL procedure successfully completed.


SQL> create index system.t1_idx on system.t1(id);


Index created.




SQL> create table system.t2(id number(10),name varchar(10));


Table created.


SQL> begin
  2      for i in 1 .. 100000 loop      
  3               insert into system.t2(id,name) select trunc(dbms_random.value(0,100000)) ,'t2'  from dual;
  4               commit;
  5      end loop;
  6  end;
  7  /


PL/SQL procedure successfully completed.


SQL> create index system.t2_idx on system.t2(id);


Index created.




SQL>  execute dbms_stats.gather_table_stats('SYSTEM','T1');




PL/SQL procedure successfully completed.


SQL>  execute dbms_stats.gather_table_stats('SYSTEM','T2');




PL/SQL procedure successfully completed.




两个表差了一个数量级


2.做一下merge测试


SQL>  select  /*+use_merge(t1,t2)*/* from system.t1,system.t2 where t1.id=t2.id;




9900 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 412793182


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


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


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


|   0 | SELECT STATEMENT    |      | 15680 |   229K|       |   426   (2)| 00:00:
06 |


|   1 |  MERGE JOIN         |      | 15680 |   229K|       |   426   (2)| 00:00:
06 |


|   2 |   SORT JOIN         |      | 10000 | 70000 |       |     8  (13)| 00:00:
01 |


|   3 |    TABLE ACCESS FULL| T1   | 10000 | 70000 |       |     7   (0)| 00:00:
01 |


|*  4 |   SORT JOIN         |      |   100K|   781K|  3160K|   418   (2)| 00:00:
06 |


|   5 |    TABLE ACCESS FULL| T2   |   100K|   781K|       |    52   (2)| 00:00:
01 |


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




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


   4 - access("T1"."ID"="T2"."ID")
       filter("T1"."ID"="T2"."ID")




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        207  consistent gets
          0  physical reads
          0  redo size
     202601  bytes sent via SQL*Net to client
       7772  bytes received via SQL*Net from client
        661  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       9900  rows processed




交换一个表


 SQL>  select  /*+use_merge(t2,t1)*/* from system.t1,system.t2 where t1.id=t2.id;




9900 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 412793182


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


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


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


|   0 | SELECT STATEMENT    |      | 15680 |   229K|       |   426   (2)| 00:00:
06 |


|   1 |  MERGE JOIN         |      | 15680 |   229K|       |   426   (2)| 00:00:
06 |


|   2 |   SORT JOIN         |      | 10000 | 70000 |       |     8  (13)| 00:00:
01 |


|   3 |    TABLE ACCESS FULL| T1   | 10000 | 70000 |       |     7   (0)| 00:00:
01 |


|*  4 |   SORT JOIN         |      |   100K|   781K|  3160K|   418   (2)| 00:00:
06 |


|   5 |    TABLE ACCESS FULL| T2   |   100K|   781K|       |    52   (2)| 00:00:
01 |


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




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


   4 - access("T1"."ID"="T2"."ID")
       filter("T1"."ID"="T2"."ID")




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        207  consistent gets
          0  physical reads
          0  redo size
     202601  bytes sent via SQL*Net to client
       7772  bytes received via SQL*Net from client
        661  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       9900  rows processed




我们也发现,交换了 use_merge(t2,t1) t1与t2的位置,但执行计划是不变的.
我们继续查看逻辑读与sorts(memory),  207 是  逻辑读 , 2 是sorts (memory)




3.做一下nest loop测试


SQL>  select  /*+ leading(t1) use_nl(t2)*/* from system.t1,system.t2 where t1.id=t2.id;




9900 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391


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


| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Tim
e     |


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


|   0 | SELECT STATEMENT             |        | 15680 |   229K| 30018   (1)| 00:
06:01 |


|   1 |  NESTED LOOPS                |        |       |       |            |
      |


|   2 |   NESTED LOOPS               |        | 15680 |   229K| 30018   (1)| 00:
06:01 |


|   3 |    TABLE ACCESS FULL         | T1     | 10000 | 70000 |     7   (0)| 00:
00:01 |


|*  4 |    INDEX RANGE SCAN          | T2_IDX |     2 |       |     1   (0)| 00:
00:01 |


|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     2 |    16 |     3   (0)| 00:
00:01 |


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




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


   4 - access("T1"."ID"="T2"."ID")




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      21883  consistent gets
          0  physical reads
          0  redo size
     220929  bytes sent via SQL*Net to client
       7772  bytes received via SQL*Net from client
        661  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9900  rows processed


我们从逻辑读与 sorts(memory) 两个指标进行对比
此次 逻辑读为  21883,显然比较高, sorts(meory) 为零,显然比较底.