Principles of sortmergejoin

These three types of table join methods are the most basic connection methods of Oracle: nested loop join (nestedloopsjoin) Principle hashjoin principle sortmerge join (sor

These three table join methods are the most basic connection methods of Oracle: nested loops join principle hash join principle sort merge join (sor

These three table join methods are the most basic connection methods for Oracle:
Principle of nested loop join
Hash join Principle

Sort merge join)

Number of visits: both tables can only be accessed 0 times or 1 time.
Whether the driver table is ordered: none.
Sort or not: Yes.
Application Scenario: When the result set has been sorted.

Sort and merge join principle: if the data in Table A is (, 2), the data in Table B is (, 1 ), first, all table A and table B are scanned and sorted as follows:

A B

1 1

2 1

2 2

4 2

5 3

Because there is no driver table, oracle will randomly select A table driver. If A is selected to scan to 1, then B is scanned. When scanning = 1, it is managed, when B = 2 is scanned, Table A is scanned with B = 2, instead of starting from 1, but starting from 2. Scanning and association are performed alternately.

Next we will do an experiment:

SQL> set linesize 1000
SQL> drop table test1 purge;
SQL> drop table test2 purge;
SQL> create table test1 as select * from dba_objects where rownum <= 100;
SQL> create table test2 as select * from dba_objects where rownum <= 1000;
SQL> exec dbms_stats.gather_table_stats (user, 'test1 ');
SQL> exec dbms_stats.gather_table_stats (user, 'test2 ');
SQL> alter session set statistics_level = all;
SQL> select/* + ordered use_merge (t2) */count (*)
From test1 t1, test2 t2
Where t1.object _ id = t2.object _ id;
COUNT (*)
----------
100

SQL> select * from table (dbms_xplan.display_cursor (null, null, 'allstats last '));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL _ID f8ffscp1kugv4, child number 0
-------------------------------------
Select/* + ordered use_merge (t2) */count (*) from test1 t1, test2 t2 where t1.object _ id = t2.object _ id

Plan hash value: 737852259

---- Explanations:

Starts is the number of times the SQL statement is executed.
E-Rows is the expected number of Rows in the execution plan.
A-Rows indicates the number of Rows actually returned. When comparing A-Rows with E-Rows, you can determine which step of the execution plan has A problem.
A-Time indicates the actual execution Time of each step (HH: MM: SS. FF). You can know the Time consumed by the SQL statement based on this line.
Buffers performs logical read or consistent read for each step.
Reads is a physical read.
OMem and 1Mem are the memory evaluation values required for execution, 0Mem is the evaluation value for the memory required for the optimal execution mode, and 1Mem is the evaluation value for the memory required for the one-pass mode.
0/1/M is the optimal number of times/one-pass/multipass is executed.
Used-Mem memory consumption
Bytes -------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
Bytes -------------------------------------------------------------------------------------------------------------------
| 1 | sort aggregate | 1 | 1 | 1 | 00:00:00. 01 | 19 |
| 2 | merge join | 1 | 100 | 100 | 00:00:00. 01 | 19 |
| 3 | sort join | 1 | 100 | 100 | 00:00:00. 01 | 4 | 2048 | 2048 | 2048 (0) |
| 4 | table access full | TEST1 | 1 | 100 | 100 | 00:00:00. 01 | 4 |
| * 5 | sort join | 100 | 1000 | 100 | 00:00:00. 01 | 15 | 73728 | 73728 |
| 6 | table access full | TEST2 | 1 | 1000 | 1000 | 00:00:00. 01 | 15 |
Bytes -------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
5-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID ")
Filter ("T1". "OBJECT_ID" = "T2". "OBJECT_ID ")

SQL> select/* + ordered use_merge (t2) */count (*)
From test1 t1, test2 t2
Where t1.object _ id = t2.object _ id
And t1.object _ id = 99999;
COUNT (*)
----------
0

For more details, please continue to read the highlights on the next page: