Oracle Table Connection------> Sort merge joins (merge sort join) __oracle

A sort merge Join is a Join method that uses sort and merge operations (merge) to obtain a connection result set when making a connection.

The pros and cons of a sorted merge join and the scenario that applies are as follows:

A, in general, the execution of a sorted merge join is far less efficient than a hash join, but the former is more widely used because the hash join can only be used for the equivalent join condition, and the sorted merge join can be used for other join conditions (such as <,<=,>.>=)

b, in general, sort merge joins are not appropriate for OLTP-type systems, essentially because sorting is a very expensive operation because of the OLTP type system, except, of course, if you can avoid sorting operations.

The sorting merge connection (merge sort Join) between the Oracle tables is characterized by the following:

1, the driver table and the driver table are only accessed at most once.

2, the table of the sorted merge joins has no drive order.

3, sorting the joined tables needs to be sorted and used to sort_area_size.

4, the sorting merge join does not apply to the join condition is: Not equal to <>,like, which is greater than, less than, is greater than or equal to >=, less than or equal to <=, is applicable to the sort merge join

5, sort the merge join, and if there is an index, you can exclude the sort.

Let me do an experiment to confirm the conclusion as follows:

The specific test base table please check my blog as follows Link:

----of the Oracle table connection nested loops (Nested Loops join)

1. Number of accesses to the driver table and the driven table:

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

Selected rows

From the experiment above you can see that the sort merge joins are the same as the hash joins, T1 and T2 tables are only accessed 0 or 1 times.

Select/*+ ordered Use_merge (T2) * * from T1,T2 where t1.id=t2.t1_id and 1=2; This statement T1 and T2 tables are accessed 0 times. I can do the test.

Summary: Sort merge joins have no concept of driving and driven tables at all, while nested loops and hash joins take into account drivers and driven tables.

2, sort the merged table's driving order

The following is the T1 execution plan for the driver table

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

Selected rows.

elapsed:00:00:00.01

The following is the T2 execution plan for the driver table:

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

Selected rows.

elapsed:00:00:00.85

As you can see from the above two execution plans, the effect is the same regardless of whether the T1 represents the driver table or the driver table, and the order size is 2048+12m and one is 12m+2048.

Conclusion: The sort merge Join has no driver concept, no matter which table is in front of it.

3, sorting restrictions on merging joins

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 "))

Rows selected.

As you can see from the execution plan above, the optimizer is walking 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 "))

Rows selected.

Similarly, it can be experimentally concluded that the join condition that the sort merge join does not apply is: Not equal to <>,like, which is greater than, less than, greater than or equal to >=, less than or equal to <=, and is applicable to sort merge joins