Hash join Principle

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

Hashjoin)

Number of visits: both the driver table and the driver table can only be accessed 0 times or once.
Whether the driver table is sequential: Yes.
Sort or not: No.
Application scenarios: 1. Join a large table and a small table;
2. There is no index on the table;
3. The returned result set is large.

The principle is simple. First, hash the associated fields of the driver table to the PGA (of course, The rowid is also in the PGA), then scan the driver table, and obtain the first data, hash the joined fields to test small tables in PGA. If the match is found, join the joined fields and obtain the second one .........

Next we will do an experiment:

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/* + leading (t1) use_hash (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
Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL _ID 3f2mts0kt82u2, child number 0
-------------------------------------
Select/* + leading (t1) use_hash (t2) */count (*) from test1 t1, test2 t2 where t1.object _ id = t2.object _ id
Plan hash value: 2544416891

---- 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 | hash join | 1 | 100 | 100 | 00:00:00. 01 | 19 | 1066K | 1066K | 1162 K (0) |
| 3 | table access full | TEST1 | 1 | 100 | 100 | 00:00:00. 01 | 4 |
| 4 | table access full | TEST2 | 1 | 1000 | 1000 | 00:00:00. 01 | 15 |
Bytes ------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID ")


SQL> select/* + leading (t1) use_hash (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:

1 2 Next Page