Oracle Table Connection-Sort merge joins sort merge connection

I. Sort merge joins connection (sort merge connection) principle

Refers to the method by which the join operation comes to the last returned result set when two table joins are sorted first by the connection column.

If the table T1 and T2 are connected by a sort merge connection, Oracle performs the following steps:
(1) Access the T1 table based on the predicate condition (if any) in the SQL statement, get a filtered result set, and then sort the result set by the join column in T1
(2) Access the T2 table based on the predicate condition (if any) in the SQL statement, get a filtered result set, and then sort the result set by the join column in T2
(3) Combining the result sets of 1 and 2, the records are matched to get the final result set.

In general, the sort merge joins connection (sort merge connection) is not widely used, because in most cases the use of nested loops or hash joins can achieve better execution efficiency, but since hash joins can only be used for equivalent connection conditions, So in the case of non-equivalent conditions and non-like "<>", if there is already a sort on the connection column, using the sort merge joins connection method can achieve better execution efficiency.

two. Sort merge joins connection (sort merge connection) Properties

(1) The driver table is accessed most once, if the independent predicate condition (not involving the function or expression that drives the table field) is not established, then no more access to the driver table
(2) The drive table is accessed up to one time. If the driver table is not logged, the driver table does not have access
(3) The selection of the driver table has no significant impact on the execution cost and performance
(4) Support most of the connection conditions, such as ">" "<" ">=" "<=", do not support like, "<>"

Tectonic test Data

sql> CREATE TABLE T1 (2 ID number not NULL, 3 n number, 4 pad VARCHAR2 (4000), 5 CONSTRAINT t1_pk PRIMA RY KEY (ID) 6); Table created.   sql> CREATE TABLE T2 (2 ID number not NULL, 3 t1_id number is 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 is 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 is 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 ', ') from dual CONNECT by level <= 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 R oWS created. Sql> INSERT into T4 SELECT 10000+rownum, T3.id, 10000+rownum, t3.pad from T3, T1 dummy ORDER by dbms_random.random;1000 0 rows created. Sql> COMMIT; Commit complete.

use hint to let execution plan take T3 as driver table

Sql> Select/*+ Leading (T3) use_merge (T4) */* 2 from T3, T4 3 where t3.id = t4.t3_id and T3.N = 1100;10 rows Selec Ted. 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 = 1100Plan 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:00.01 |  15 |  2048 | 2048 | 2048 (0) | |    * 3 | TABLE ACCESS full|      T3 |      1 |      1 |      1 |00:00:00.01 |       15 |       |          | ||   * 4 |      SORT JOIN |      |  1 |     10000 |     10 |00:00:00.02 |   104 |   974k|  535k|   865K (0) | |    5 | TABLE ACCESS full|      T4 |  1 |  10000 |     10000 |00:00:00.01 |       104 |       |          | |-------------------------------------------------------------------------------------------------------------- ---predicate information (identified by Operation ID):---------------------------------------------------3-filter (" T3 "." N "=1100) 4-access (" T3 "." ID "=" T4 "." t3_id ") filter (" T3 "." ID "=" T4 "." t3_id ")

use hint to let execution plan take T4 as driver table

Sql> Select/*+ leading (T4) use_merge (T3) */* 2 from T3, T4 3 where t3.id = t4.t3_id and T3.N = 1100;10 rows Selec Ted. Sql> select * FROM table (Dbms_xplan.display_cursor (Null,null, ' allstats last ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ----------------------------------sql_id Gxuwn06y1c1az, child number 0-------------------------------------Select/ *+ leading (T4) use_merge (T3) * * from T3, T4 where t3.id =t4.t3_id and T3.N = 1100Plan hash value:875334572------------- ----------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-rows |   A-rows | A-time |  Buffers |  Omem | 1Mem | Used-mem |-----------------------------------------------------------------------------------------------------------------| 0 |      SELECT STATEMENT |      |        1 |     |     10 |00:00:00.04 |       119 |       |          |   ||  1 |      MERGE JOIN |      |     1 |     10 |     10 |00:00:00.04 |       119 |       |          |   ||   2 |      SORT JOIN |      |  1 |   10000 |     1001 |00:00:00.04 |   104 |   974k|  535k|   865K (0) | |    3 | TABLE ACCESS full|      T4 |  1 |  10000 |     10000 |00:00:00.01 |       104 |       |          | ||   * 4 |      SORT JOIN |   |      1001 |     1 |      10 |00:00:00.01 |  15 |  2048 | 2048 | 2048 (0) | |    * 5 | TABLE ACCESS full|      T3 |      1 |      1 |      1 |00:00:00.01 |       15 |       |          | |-------------------------------------------------------------------------------------------------------------- ---predicate information (identified by Operation ID):---------------------------------------------------4-access (" T3 "." ID "=" T4 "." t3_id ") filter (" T3 "." ID "=" T4 "." t3_id ") 5-filter (" T3 ")." N "=1100)

From the results of the returned execution plan we can see:
1. The cost (a-time) and buffers of the T3 are the same as the driver table and the T4 as the driver table.

2. With T3 as the driver table, T3 Access once, T4 is also visited once; With T4 as the driver table, T4 Access once, T3 is also visited once

3. Sorting required if the PGA space is heavy enough to sort in the PGA, if not enough then swap to disk for sorting

In addition, there are several statistics in the Execution Plan column 0Mem, 1Mem, Use_mem need to introduce

• 0Mem refers to the amount of memory that is expected to be sorted in the PGA
• 1Mem refers to the amount of memory that is expected to swap data once to disk space when the memory size (PGA) is not sufficient for sorting
• Used-mem refers to the amount of memory actually used at execution time, where the number in parentheses represents the number of times the disk is exchanged, and 0 means no disk swapping

three. Sort merge joins connection (sort merge connection) optimization

Sql> Select/*+ Leading (T3) use_merge (T4) */* 2 from T3, T4 3 where t3.id = t4.t3_id and T3.N = 1100 and T4.N = 10 034; Sql> select * FROM table (Dbms_xplan.display_cursor (Null,null, ' allstats last ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------sql_id Bg9h60c7ak3ud, child number 0-------------------------------------select/*+ Leading (T3) use _merge (T4) */* from T3, T4 where t3.id =t4.t3_id and T3.N = 1100 and T4.N = 10034Plan hash value:3831111046------------- ----------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-rows |   A-rows | A-time |  Buffers |  Omem | 1Mem | Used-mem |-----------------------------------------------------------------------------------------------------------------| 0 |      SELECT STATEMENT |      |        1 |      |     1 |00:00:00.01 |       119 |       |          |   ||  1 |      MERGE JOIN |      |      1 |      1 |     1 |00:00:00.01 |       119 |       |          |   ||   2 |      SORT JOIN |      |      1 |      1 |      1 |00:00:00.01 |  15 |  2048 | 2048 | 2048 (0) | |    * 3 | TABLE ACCESS full|      T3 |      1 |      1 |      1 |00:00:00.01 |       15 |       |          | ||   * 4 |      SORT JOIN |      |      1 |      1 |     1 |00:00:00.01 |  104 |  2048 | 2048 | 2048 (0) | |    * 5 | TABLE ACCESS full|      T4 |      1 |      1 |     1 |00:00:00.01 |       104 |       |          | |-------------------------------------------------------------------------------------------------------------- ---predicate information (identified by Operation ID):---------------------------------------------------3-filter (" T3 "." N "=1100) 4-access (" T3 "." ID "=" T4 "." t3_id ") filter (" T3 "." ID "="T4 "." t3_id ") 5-filter (" T4 "." N "=10034) sql> CREATE index t4_n on t4 (n); index created. Sql> Select/*+ Leading (T3) use_merge (T4) */* 2 from T3, T4 3 where t3.id = t4.t3_id and T3.N = 1100 and T4.N = 10 034; Sql> select * FROM table (Dbms_xplan.display_cursor (Null,null, ' allstats last ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------sql_id Bg9h60c7ak3ud, child number 0------------------------------ -------Select/*+ Leading (T3) use_merge (T4) * * from T3, T4 where t3.id =t4.t3_id and T3.N = 1100 and T4.N = 10034Plan ha SH value:1501658231-------------------------------------------------------------------------------------------- ----------------------------------------| Id | Operation | Name | Starts | E-rows | A-rows | A-time | Buffers |  Reads |  Omem | 1Mem | Used-mem |------------------------------------------------------------------------------------------------------   ------------------------------| 0 |      SELECT STATEMENT |      |        1 |      |      1 |00:00:00.01 |      18 |       1 |       |          |   ||  1 |      MERGE JOIN |      |      1 |      1 |      1 |00:00:00.01 |      18 |       1 |       |          |   ||   2 |      SORT JOIN |      |      1 |      1 |      1 |00:00:00.01 |      15 |  0 |  2048 | 2048 | 2048 (0) | |    * 3 | TABLE ACCESS Full |      T3 |      1 |      1 |      1 |00:00:00.01 |      15 |       0 |       |          | ||   * 4 |      SORT JOIN |      |      1 |      1 |       1 |00:00:00.01 |      3 |  1 |  2048 | 2048 |   2048 (0) | |    5 | TABLE ACCESS by INDEX rowid|      T4 |      1 |      1 |       1 |00:00:00.01 |      3 |       1 |       |          | ||     * 6 | InchDEX RANGE SCAN |      T4_n |      1 |      1 |       1 |00:00:00.01 |      2 |       1 |       |          | |-------------------------------------------------------------------------------------------------------------- ----------------------predicate information (identified by Operation ID):------------------------------------------ ---------3-filter ("T3". N "=1100) 4-access (" T3 "." ID "=" T4 "." t3_id ") filter (" T3 "." ID "=" T4 "." t3_id ") 6-access (" T4 "." N "=10034) sql> CREATE index t3_n on t3 (n); index created. Sql> Select/*+ Leading (T3) use_merge (T4) */* 2 from T3, T4 3 where t3.id = t4.t3_id and T3.N = 1100 and T4.N = 10 034; Sql> select * FROM table (Dbms_xplan.display_cursor (Null,null, ' allstats last ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------sql_id Bg9h60c7ak3ud, child number 0------------------------------------- Select/*+ Leading (T3) use_merge (T4) */* from T3, T4 where t3.id =t4.t3_id and T3.N = 1100 and T4.N = 10034Plan Hash Valu e:1827980052-------------------------------------------------------------------------------------------------- ----------------------------------| Id | Operation | Name | Starts | E-rows |   A-rows | A-time | Buffers |  Reads |  Omem | 1Mem | Used-mem |------------------------------------------------------------------------------------------------------   ------------------------------| 0 |      SELECT STATEMENT |      |        1 |      |       1 |00:00:00.01 |      6 |       1 |       |          |   ||  1 |      MERGE JOIN |      |      1 |      1 |       1 |00:00:00.01 |      6 |       1 |       |          |   ||   2 |      SORT JOIN |      |      1 |      1 |      1 |00:00:00.01 | 3 |  1 |  2048 | 2048 |   2048 (0) | |    3 | TABLE ACCESS by INDEX rowid|      T3 |      1 |      1 |       1 |00:00:00.01 |      3 |       1 |       |          | ||     * 4 | INDEX RANGE SCAN |      T3_n |      1 |      1 |       1 |00:00:00.01 |      2 |       1 |       |          | ||   * 5 |      SORT JOIN |      |      1 |      1 |       1 |00:00:00.01 |      3 |  0 |  2048 | 2048 |   2048 (0) | |    6 | TABLE ACCESS by INDEX rowid|      T4 |      1 |      1 |       1 |00:00:00.01 |      3 |       0 |       |          | ||     * 7 | INDEX RANGE SCAN |      T4_n |      1 |      1 |       1 |00:00:00.01 |      2 |       0 |       |          | |-------------------------------------------------------------------------------------------------------------- ----------------------predicate information (identified by Operation ID):------------------------------------------ ---------4-access ("T3". N "=1100) 5-access (" T3 "." ID "=" T4 "." t3_id ")      Filter ("T3". " ID "=" T4 "." t3_id ") 7-access (" T4 "." N "=10034)

As can be seen from the execution plan above, the last buffer used after the full table scan is 119, the index is indexed on a single table after using the index range scan, buffer is 18, and the index established on two tables uses the index range scan after buffer is 6. It can be seen that if there is an index on the predicate condition of the table, the execution efficiency will be improved.

Also, because the sort merge joins needs to be sorted in the PGA first, if the PGA space is low, the data is swapped to disk for sorting. Because disks are slow devices relative to memory, sorting on a disk is slower than sorting in memory, and the time it takes to sort the sort will also add to the amount of time the data travels on memory and disk, so minimizing the number of disk sorts will increase execution efficiency, and there are two ways to reduce the disk ordering:

1. Increase the size of the PGA, if it is Oracle 10g, you need to increase the size of the parameter pga_aggregate_target, if it is Oracle 11g, increase the size of Memory_target

2. Reduce the amount of data to be sorted, some unwanted fields should not be written behind select

Four. Summary

When SQL tuning is encountered, if the Execution Plan display table is connected by the sort merge join:

First, let's see if the SQL statement is a table connection and it's possible to convert to a hash join (equivalent join condition)

Second, you can only use the sort merge join to see if there is an index on the predicate condition of the table

Finally, see if the memory size used by the execution plan sort is not sorted on disk, and is not able to avoid sorting on disk

Reference: << Oracle-based SQL optimization >>

<< Harvest, more than oracle>>

<<troubleshooting Oracle Performance>>

Oracle Table Connection-Sort merge joins sort merge connection