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.

[hichamlayadi]

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

[maggot]

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

[taanhminh]

This is very easy to understand. Thank you

[Lukky]

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 ?

[bhausahebjadhav]

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?

[anushreesharma]

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

[adamasmal]

Why can hash joins only be used for equijoins