|
Explain Nested Join, Hash Join, and Merge Join in SQL Query Plan.Nested Loops Joins
- Nested loops join an outer data set to an inner data set.
- For each row in the outer data set that matches the single-table predicates, the database retrieves all rows in the inner data set that satisfy the join predicate.
- If an index is available, then the database can use it to access the inner data set by rowid.
Hash Joins
- The database uses a hash join to join larger data sets.
- The optimizer uses the smaller of two data sets to build a hash table on the join key in memory, using a deterministic hash function to specify the location in the hash table in which to store each row.
- The database then scans the larger data set, probing the hash table to find the rows that meet the join condition.
Sort Merge Joins
- A sort merge join is a variation on a nested loops join.
- The database sorts two data sets (the SORT JOIN operations), if they are not already sorted.
- For each row in the first data set, the database probes the second data set for matching rows and joins them (the MERGE JOIN operation), basing its start position on the match made in the previous iteration.
|
