Hierarchical Symmetric Hash Join for Memory-Constrained One-to-One Joins
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
One-to-one join operations in relational database systems are resource-intensive due to high memory and I/O costs, especially when dealing with large tables and limited memory, as existing algorithms do not effectively consider data distribution or allow for user-defined memory control.
Innovation Solution
The hierarchical symmetric hash join algorithm reduces memory usage by maintaining one-entry tables and hash tables for each table, allowing immediate output of matched tuples and adjusting memory usage through the α-algorithm, which considers data distribution patterns to optimize block reading and reduce disk access.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If hash join algorithm is used with limited memory, then memory usage is reduced, but I/O access cost increases three times compared to infinite memory scenario
Solution Approach 1:
The algorithm segments the join operation into multiple phases based on data distribution patterns. It divides the larger table into partitions and processes them in batches, allowing the build table to be evicted and reused for different partition pairs. This segmentation enables efficient memory utilization while reducing redundant I/O operations compared to traditional limited-memory hash join.
Solution Approach 2:
The algorithm performs preliminary analysis of data distribution patterns before executing the join operation. By predicting whether data is uniformly distributed, clustered, or reverse-ordered, it can pre-determine the optimal join strategy and memory management approach, avoiding unnecessary I/O operations and hash table rebuilds that would occur with naive approaches.
2Productivity
If existing join algorithms are used, then join operation can be performed, but they do not effectively consider data distribution patterns leading to suboptimal performance
Solution Approach 1:
The algorithm dynamically adapts its behavior based on detected data distribution patterns. It switches between different processing modes (uniform distribution mode, clustered mode, reverse-ordered mode) depending on the actual data characteristics. This dynamic adaptation allows the algorithm to optimize performance for each specific scenario rather than using a fixed approach that works suboptimally across all cases.
Solution Approach 2:
The algorithm incorporates feedback mechanisms that monitor data access patterns and matching rates during execution. Based on this feedback, it adjusts memory management decisions, such as when to evict the build table and when to retain it, and modifies the order of processing partitions to maximize cache utilization and minimize I/O operations.
3Productivity
If traditional hash join is used with sufficient memory, then only two table reads are needed, but this requires infinite memory which is not available in practice
Solution Approach 1:
The algorithm implements a nested structure where partitions of the larger table are processed in batches, and within each batch, the build table is reused across multiple probe operations. This nesting allows the system to achieve near-optimal I/O performance (close to the two-read ideal) while using limited memory, as the build table serves multiple purposes across different partition pairs rather than requiring separate build tables for each partition.
Data Source
AI summary
One-to-one join is widely used in machine learning and business intelligent applications. Disclosed herein is an efficient method for one-to-one join to reduce memory usage and thus disk I/O accesses with limited memory. Disclosed method outputs and removes a pair of tuples immediately when they are matched to each other to allow join results to be generated without reading entire tables. Meanwhile, disclosed method increases matching rate for in memory blocks through the predication of data distribution patterns based on both statistics and history block matching information.