oracle表连接------>排序合并连接(Merge Sort Join)

时间:2022-09-18 20:00:27

转载:http://blog.csdn.net/waterxcfg304/article/details/25873265

排序合并连接 (Sort Merge Join)是一种两个表在做连接时用排序操作(Sort)和合并操作(Merge)来得到连接结果集的连接方法。

对于排序合并连接的优缺点及适用场景如下:

a,通常情况下,排序合并连接的执行效率远不如哈希连接,但前者的使用范围更广,因为哈希连接只能用于等值连接条件,而排序合并连接还能用于其他连接条件(如<,<=,>.>=)

b,通常情况下,排序合并连接并不适合OLTP类型的系统,其本质原因是对于因为OLTP类型系统而言,排序是非常昂贵的操作,当然,如果能避免排序操作就例外了。

 

oracle表之间的连接之排序合并连接(Merge Sort Join),其特点如下:

1,驱动表和被驱动表都是最多只被访问一次。

2,排序合并连接的表无驱动顺序。

3,排序合并连接的表需要排序,用到SORT_AREA_SIZE。

4,排序合并连接不适用于的连接条件是:不等于<>,like,其中大于>,小于<,大于等于>=,小于等于<=,是可以适用于排序合并连接

5,排序合并连接,如果有索引就可以排除排序。

 

下面我来做个实验来证实如上的结论:

具体的测试基础表请查看本人Blog 如下链接:

oracle表连接之----〉嵌套循环(Nested Loops Join)

 

1,驱动表和被驱动表的访问次数:

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

SQL> select sql_id, child_number, sql_text from v$sql where sql_text like '%use_merge%';
 
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
85u4h9hfqa5ar            0  select sql_id, child_number, sql_text from v$sql where sql_text like '%use_merg
6xph9fhapys39            0  select /*+ ordered use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id
 
SQL> select * from table(dbms_xplan.display_cursor('6xph9fhapys39',0,'allstats last'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6xph9fhapys39, child number 0
-------------------------------------
 select /*+ ordered use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id
Plan hash value: 412793182
--------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buf
--------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |    100 |    100 |00:00:00.07 |
|   2 |   SORT JOIN         |      |      1 |    100 |    100 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |
|*  4 |   SORT JOIN         |      |    100 |    100K|    100 |00:00:00.07 |
|   5 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."ID"="T2"."T1_ID")
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       filter("T1"."ID"="T2"."T1_ID")
Note
-----
   - dynamic sampling used for this statement
 
26 rows selected

从上面的实验可以看出排序合并连接和HASH连接时一样的,T1和T2 表都只会被访问0次或者1次。

select /*+ ordered use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id and 1=2;此语句T1和T2表就会是被访问0次。自己可以做试验测试下。

总结:排序合并连接根本就没有驱动和被驱动表的概念,而嵌套循环连接和哈希连接就要考虑驱动和被驱动表的情况!!

 

2,排序合并的表的驱动顺序

下面是T1为驱动表的执行计划

select /*+ leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20;
select sql_id,child_number,sql_text from v$sql where sql_text like '%from t1,t2 where t1.id=t2.t1_id and t1.num=20%';
SQL> select * from table(dbms_xplan.display_cursor('8z4jvhnnfhxyf',0,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8z4jvhnnfhxyf, child number 0
-------------------------------------
select /*+ leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20

Plan hash value: 412793182

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:00.58 |    3462 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |      1 |      1 |00:00:00.01 |       6 |  2048 |  2048 |2048  (0)|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  3 |    TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|*  4 |   SORT JOIN         |      |      1 |    100K|      1 |00:00:00.58 |    3456 |    14M|  1490K|  12M (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |    3456 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

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

   3 - filter("T1"."NUM"=20)
   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")

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


23 rows selected.

Elapsed: 00:00:00.01

下面是T2为驱动表的执行计划:

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bxydvw58bhczf, child number 0
-------------------------------------
select /*+ leading(t2) use_merge(t1)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20

Plan hash value: 1792967693

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:02.20 |    3462 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |    100K|     21 |00:00:02.20 |    3456 |    14M|  1490K|  12M (0)|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   3 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.10 |    3456 |       |       |          |
|*  4 |   SORT JOIN         |      |     21 |      1 |      1 |00:00:00.01 |       6 |  2048 |  2048 |2048  (0)|
|*  5 |    TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

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

   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")
   5 - filter("T1"."NUM"=20)

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


23 rows selected.

Elapsed: 00:00:00.85

从上面的两个执行计划可以看出,无论T1表示驱动表还是被驱动表,效果都是一样的,排序的尺寸一个是2048+12M,一个是12M+2048。

结论:排序合并连接没有驱动的概念,无论哪个表再前面都无所谓。

 

3,排序合并连接的限制

SQL〉explain plan for select /*+ leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id<>t2.t1_id and t1.num=20;

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4016936828

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  5000 |  1083K| 82709   (1)| 00:15:10 |
|   1 |  NESTED LOOPS      |      |  5000 |  1083K| 82709   (1)| 00:15:10 |
|   2 |   TABLE ACCESS FULL| T2   |   100K|    10M|   710   (1)| 00:00:08 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |   107 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------


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

   3 - filter("T1"."NUM"=20 AND TO_CHAR("T1"."ID") LIKE
              TO_CHAR("T2"."T1_ID"))

16 rows selected.

 从上面的执行计划可以看出,优化器走的是NESTED LOOPS JOIN。

SQL> explain plan for  select /*+ leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id>t2.t1_id and t1.num=20;

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 412793182

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  5000 |  1083K|       |  5080   (1)| 00:00:56 |
|   1 |  MERGE JOIN         |      |  5000 |  1083K|       |  5080   (1)| 00:00:56 |
|   2 |   SORT JOIN         |      |     1 |   107 |       |     4  (25)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T1   |     1 |   107 |       |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |   100K|    10M|    25M|  5076   (1)| 00:00:56 |
|   5 |    TABLE ACCESS FULL| T2   |   100K|    10M|       |   710   (1)| 00:00:08 |

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

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

   3 - filter("T1"."NUM"=20)
   4 - access(INTERNAL_FUNCTION("T1"."ID")>INTERNAL_FUNCTION("T2"."T1_ID"))
       filter(INTERNAL_FUNCTION("T1"."ID")>INTERNAL_FUNCTION("T2"."T1_ID"))

19 rows selected.

同理可以实验得出:排序合并连接不适用于的连接条件是:不等于<>,like,其中大于>,小于<,大于等于>=,小于等于<=,是可以适用于排序合并连接