Hash Joins and Symmetric Hash Joins in Database Queries
Tutorial - 4
Hash Join & Symmetric Hash Join
What is a JOIN
•
Between tables or self
•
Query accessing multiple rows of the same or
different tables
•
Order of execution : Sequential
Hash Join
•
Loads candidate records from one side of the
join into a hash table.
•
Probe for each record from other side of the
join
•
Purpose of the Hash Table - Indexing
Hash Join
Facts about hash joins
•
Hash joins do not need indexes on predicates
•
Reduce the hash table size to improve the
performance
•
Cannot perform joins that have range
conditions in the join predicates
Drawbacks with Hash Join
•
Bottleneck in query execution pipeline
•
Sufficient memory required to store inner
relation.
•
Hybrid Hash Join!!!
Symmetric Hash Join
•
Maintains two hash tables
•
Two Hash functions
Other requirements to be considered
•
Should run until it give one output tuple each
time
•
State should be saved – should include detail
on which tuple is running using state node.
Hash joins and symmetric hash joins are key techniques used in database queries to efficiently access and combine data from multiple tables. Hash joins load candidate records into hash tables for quick probing, while symmetric hash joins maintain two hash tables with two hash functions. Despite their advantages, drawbacks such as bottleneck in query execution pipeline and memory requirements exist. Considerations like maintaining state for each output tuple are essential when working with these join types.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Tutorial - 4 Hash Join & Symmetric Hash Join
What is a JOIN Between tables or self Query accessing multiple rows of the same or different tables Order of execution : Sequential
Hash Join Loads candidate records from one side of the join into a hash table. Probe for each record from other side of the join Purpose of the Hash Table - Indexing
Facts about hash joins Hash joins do not need indexes on predicates Reduce the hash table size to improve the performance Cannot perform joins that have range conditions in the join predicates
Drawbacks with Hash Join Bottleneck in query execution pipeline Sufficient memory required to store inner relation. Hybrid Hash Join!!!
Symmetric Hash Join Maintains two hash tables Two Hash functions
Other requirements to be considered Should run until it give one output tuple each time State should be saved should include detail on which tuple is running using state node.
