Oracle table join-sortmergejoins sort merge join

Oracle table join-sortmergejoins sort merge join
I. sort merge joins connection (sort and merge connections) Principle

When two tables are connected, the join columns are sorted separately and then merged to obtain the final returned result set.

Assume that table T1 and table T2 are connected by sorting and merging. The oracle execution steps are as follows:
(1) Access Table T1 Based on the predicate condition (if any) in the SQL statement to obtain a filtered result set and sort the result set according to the connection column in table T1.
(2) Access Table T2 Based on the predicate condition (if any) in the SQL statement to obtain a filtered result set and sort the result set according to the connection column in table T2.
(3) combine the results of 1 and 2 to match the record to obtain the final result set.

Generally, sort merge joins connections are not widely used, because nested loops or hash joins can achieve better execution efficiency in most cases, however, because hash joins can only be used for equijoin conditions, if a non-equijoin condition is connected and a non-like non-"<>" column is sorted, using the sort merge joins connection method can achieve better execution efficiency.
Ii. sort merge joins connection (sort and merge connections) Features

(1) The driver table can be accessed at most once. If an independent predicate condition (a function or expression that does not involve a field in the driver table) is invalid, you do not need to access the driver table any more.
(2) The driver table can be accessed at most once. If the driver table has no records, the driver table does not need to be accessed.
(3) The selection of the driver table has no significant impact on the execution cost and performance.
(4) supports most of the connection conditions, such as ">" "<"> = "" <= ", not like," <>"

Construct Test Data

SQL> CREATE TABLE t1 (  2    id NUMBER NOT NULL,  3    n NUMBER,  4    pad VARCHAR2(4000),  5    CONSTRAINT t1_pk PRIMARY KEY(id)  6  );Table created.SQL> CREATE TABLE t2 (  2    id NUMBER NOT NULL,  3    t1_id NUMBER 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 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 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',50) FROM dual CONNECT BY level <= 10 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 rows created.SQL> INSERT INTO t4 SELECT 10000+rownum, t3.id, 10000+rownum, t3.pad FROM t3, t1 dummy ORDER BY dbms_random.random;10000 rows created.SQL> COMMIT;Commit complete.

Use hint to make the execution plan T3 as the driving 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 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 = 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 make the execution plan take T4 as the driving 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 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 = 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)

We can see from the returned execution plan results:
1. Take T3 as the driving table and T4 as the driving table. Both cost (A-Time) and buffers are similar.

2. When T3 is used as the driving table, T3 is accessed once, and T4 is also accessed once. When T4 is used as the driving table, T4 is accessed once, and T3 is also accessed once.

3. Sorting is required. If the PGA space is too heavy, it is sorted in the PGA. If not, it is switched to the disk for sorting.

In addition, there are several statistical columns in the execution plan: 0Mem, 1Mem, and Use_Mem.
0 mem indicates the memory size required for sorting in PGA. 1 mem indicates that when the memory size (PGA) is insufficient, the expected memory size for one data exchange to the disk space Used-Mem refers to the actual memory size Used during execution. The number in parentheses indicates the number of disk exchanges, 0 indicates that disk exchange is not performed.
Iii. sort merge joins connections (sort and merge connections) 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 = 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 = 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 = 10034Plan 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 = 10034Plan 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)

From the preceding execution plan, we can see that the last buffer used after full table scan is 119. After an index is created on a table and the index range is scanned, the buffer size is 18, the index created on the two tables is scanned using the index range, and the buffer is 6. it can be seen that the execution efficiency will be improved if the table's predicate conditions are indexed.

In addition, because the sort merge joins needs to be sorted in the PGA, if the PGA space is insufficient, the data will be exchanged to the disk for sorting. Because the disk is a slow device relative to the memory, sorting on the disk is slower than sorting on the memory, in addition, the time consumed by sorting also needs to be added to the memory and the time transferred on the disk. Therefore, minimizing the number of disk sorting times will increase the execution efficiency. There are two ways to reduce disk sorting:

1. Increase the PGA size. If it is oracle 10g, you need to increase the pga_aggregate_target parameter. If it is oracle 11g, the memory_target size is increased.

2. Reduce the data size of sorting. Do not write unnecessary fields after select.

 

Iv. Summary

In case of SQL optimization, if the execution plan shows that the table connection mode is sort merge join:

First, check whether the SQL statement is possible to convert the table connection mode to hash join (equivalent join condition)

Second, you can only use sort merge join to check whether the table's predicate conditions have indexes.

Finally, check whether the memory size occupied by the execution plan is sorted on the disk or not.