Connections between Oracle tables------> Sort Merge Connection (merge sort Join)

Sort merge joins (sort merge join) are two tables that use the sort operation (sort) and merge operation (merge) to get the connection method of the result set when making a connection.

The pros and cons of a sort merge connection and the scenarios that apply are as follows:

A, typically, a sort merge connection performs much less efficiently than a hash connection, but the former is more widely used because hash joins can only be used for the equivalent join condition, while a sort merge connection can also be used for other join conditions (such as <,<=,>.>=)

b, in general, sort merge connections are not suitable 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.

A sort merge connection for connections between Oracle Tables (merge sort join), with the following characteristics:

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

2, the table with the sort merge connection has no drive order.

3, the sorted merge join table needs to be sorted and used to sort_area_size.

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

5, sort the merge connection, and if you have 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 following links:

----of Oracle table joins nested loops (Nested Loops Join)

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

Rows selected

As you can see from the experiment above, the T1 and T2 tables are only accessed 0 or 1 times, just as they do when sorting merge connections and hash joins.

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

Summary: The sort merge connection does not have the concept of drive and driven table at all, while nested loops and hash connections are to consider the situation of driving and being driven table!!