Oracle table joins-sort merge joins sorting merge joins __oracle

A. Sort merge joins connection (sort Merge Join) principle

Refers to the method by which the merge operation arrives at the last returned result set when two tables are connected, sorted separately by the join column.

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

in general, the sort merge joins join (sorted join) is not widely used because in most cases nested loops or hash joins can achieve better execution efficiency, but because the hash joins can only be used for equivalent Connection conditions, so in the case of non-equivalence conditional connections and non-like "<>" cases, if there is already a sort on the connection column, the sort merge joins join method can achieve better execution efficiency

two. Sort merge joins connection (sort merges join) attributes

(1) Driver tables are accessed at most once, if independent predicate conditions (functions or expressions that do not involve driving table fields, etc.) is not established, you do not have to access the driver table
(2) The driver table is the most frequently accessed. If the driver table is not logged, the driver table does not have access to the
(3) Driver table selection has no significant impact on execution cost and performance
(4) Support for most connection conditions, such as ">" ;" ">=" "<=", does not support like, "<>"

constructs test data

sql> CREATE TABLE T1 (2 ID number not NULL, 3 n number, 4 pad VARCHAR2 (4000), 5 CONSTRAINT t1_pk P

Rimary 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 (40

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

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


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 ', m) from dual CONNECT by level <=

_random.random;

Ten rows created.

Sql> INSERT into T2 SELECT 100+rownum, t1.id, 100+rownum, t1.pad to T1, T1 dummy order by Dbms_random.random;

Rows created.

sql> INSERT into T3 SELECT 1000+rownum, T2.id, 1000+rownum, T2.pad to T2, T1 dummy order by Dbms_random.random;

1000 rows created.

Sql> INSERT into T4 SELECT 10000+rownum, T3.id, 10000+rownum, t3.pad to T3, T1 dummy order by Dbms_random.random;

10000 rows created.

Sql> COMMIT; Commit complete.

use hint to let the execution plan take T3 as the driver table

Sql> Select/*+ Leading (T3) use_merge (T4) * * 2 from T3, T4 3 where t3.id = t4.t3_id and T3.N = 1100;


Ten rows selected.


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 = 1100 Plan Hash value:383111 1046----------------------------------------------------------------------------------------------------------- ------
| 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-filt ER ("T3".) N "=1100" 4-access ("T3"). ID "=" T4 "." T3_ID ") filter (" T3 ".) ID "=" T4 "." t3_id ")

use hint to let the execution plan take T4 as the driver table

Sql> Select/*+ leading (T4) use_merge (T3) * * 2 from T3, T4 3 where t3.id = t4.t3_id and T3.N = 1100;


Ten rows selected.


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 = 1100 Plan Hash V alue: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-acce SS ("T3".) ID "=" T4 "." t3_id ") filter("T3".) ID "=" T4 "." t3_id ") 5-filter (" T3 "). N "=1100)

From the result of the return execution plan, we can see:
1. T3 as the driving table and T4 as the driving table, both cost (a-time) and buffers are almost

2. To T3 as the driving table, T3 access once, T4 is also accessed once; T4 as the driver table, T4 Access once, T3 is also accessed once

3. Need to sort, if the PGA space is heavy enough in the PGA to sort, not if not enough to exchange to the disk sort
In addition, there are several statistics listed in the Implementation plan 0Mem, 1Mem, Use_mem need to introduce
0Mem refers to the amount of memory expected to be sorted in the PGA 1Mem refers to the amount of memory that is expected to be exchanged once to disk space when the memory size (PGA) is not sufficient for sorting Used-mem refers to the amount of memory that is actually used at execution time, where the number in parentheses represents the number of disk exchanges, and 0 represents no disk swap
three. Sort merge joins connection (sort merging join) 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 =


10034;


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 = 10034 Plan 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-filt ER ("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 =


10034;


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 = 10034 Plan hash 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 | INDEX 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 =


10034;


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 = 10034 Plan Hash value: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 you can see from the execution plan above, the last buffer used after the full table scan was 119, and the index was indexed on one table. Using the index range scan, the buffer is 18, the index established on two tables uses the index range scan and the buffer is 6. From this we can see that the predicate condition in the table will improve execution efficiency if there is an index on it.

In addition, because the sort merge joins needs to be sorted first in the PGA, data is exchanged to disk for sorting if the PGA space is insufficient. Because disks are slow devices relative to memory, as a result, sorting on disk is slower than on-memory, and the sort-ordering takes time to add data to memory and disk transfers, so minimizing the number of disk sorts can increase execution efficiency, and there are two ways to reduce disk sorting:

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 ordered, some fields do not need to write behind the select

Four. Summary

when you encounter SQL tuning, if the execution plan shows that the table is connected by the sort merge join:

first, see if the SQL statement is not a way to join the table is it possible to convert to a hash join (equivalent join condition)

Second, you can only use the sort merge join to see if the predicate condition of the table is indexed

Finally, take a look at the amount of memory that is consumed by the execution of the plan 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>>