A Look at Three Join Methods: Hash, Merge, Nested Loops

In this episode of Ask TOM Office Hours, we looked at the three join methods:
Hash joins
Merge joins
Nested loops
We covered how these work and their relative strengths and weaknesses
Highlights include:
02:35 - Example queries for unindexed joins
10:30 - How a hash join works
13:40 - How a merge join works
21:05 - How a nested loop works
28:35 - Example joins in top-N queries
45:15 - How indexes affect joins
49:00 - Example joins wheen filtering one table
52:10 - How adaptive plans work
53:30 - Summary; time complexity of the joins, how indexes affect them, and their optimal use case
AskTOM Office Hours offers free, monthly training and tips on how to make the most of Oracle Database, from Oracle product managers, developers and evangelists.
asktom.oracle....
Oracle Developers portal: developer.orac...
Sign up for an Oracle Cloud trial: cloud.oracle.c...

[@hichamlayadi8364]

That last slide should be on every CS curriculum. Thank you for making such a paramount topic easy to digest

[@maggot1234]

This is the best channel, please keep doing what you are doing!

[@Lukky2266]

In any language, during hash methods, why the values are converted using a hash algorithm and then compared, why can't they compared directly with original values ?

[@bhausahebjadhav5043]

Two large tables with no indexes on the common columns need to be joined and the output has to be in the ascending order of the common columns. Which of the following is suitable join method for this scenario?

[@taanhminh88]

This is very easy to understand. Thank you

[@anushreesharma6167]

Please make a video or a short on “driving” tables in a join. How would oracle choose one ?

[@adamasmal4020]

Why can hash joins only be used for equijoins

[@yusantriananda7829]

Thank you!