Hash Join Duplicate Elimination via Concurrent Processing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face challenges in efficiently eliminating duplicates during hash join operations, leading to increased post-processing costs and reduced query performance in very large databases, as existing methods often require separate duplicate elimination steps after join operations.
Innovation Solution
The technique combines duplicate removal with hash join processing, allowing for concurrent duplicate elimination during join operations by applying hashing techniques to data structures associated with join operations, specifically targeting duplicate-insignificant query blocks and limiting post-join references to a subset of join keys.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Manufacturing precision
If separate duplicate elimination steps are performed after hash join operations, then duplicate rows are removed from query results, but post-processing costs increase and query performance decreases
Solution Approach 1:
The patent combines duplicate elimination with hash join operations by integrating the DISTINCT operation into the hash join execution. During the hash join process, the system builds hash tables and probes for matching rows while simultaneously eliminating duplicates by tracking seen combinations of join keys and non-key columns. This merging of operations eliminates the need for separate post-processing duplicate elimination steps, reducing computational overhead and improving query performance.
Solution Approach 2:
The patent performs duplicate elimination preliminarily during the hash join operation rather than as a subsequent step. By integrating duplicate detection and elimination logic into the hash table building and probing phases, the system removes duplicates in advance before results are returned to the user. This preliminary action prevents the accumulation of duplicate rows that would otherwise require expensive post-processing.
2Loss of energy
If duplicate elimination is performed concurrently with hash join operations, then post-processing costs are reduced, but the complexity of the join operation increases
Solution Approach 1:
The patent merges duplicate elimination logic with existing hash join operations by reusing the hash table data structures and execution flow. The system enhances the standard hash join algorithm with additional bookkeeping mechanisms (such as tracking seen row combinations) without fundamentally changing the join operation's core functionality. This approach adds complexity only where necessary while leveraging existing infrastructure.
Solution Approach 2:
The hash join operation performs duplicate elimination as part of its own execution process rather than relying on external post-processing. The join operation itself generates and returns deduplicated results by maintaining state information during execution, making the duplicate elimination service self-contained within the join operation's workflow.
3Productivity
If the driving table is selected as the build side for hash join, then join performance is optimized, but duplicates from the driven table are not eliminated
Solution Approach 1:
The patent extends duplicate elimination beyond the traditional single-table scope by considering combinations of columns from both the driving table (build side) and the driven table (probe side). Instead of eliminating duplicates based solely on columns from one table, the system tracks multi-dimensional combinations of join keys and non-key columns from both tables, enabling comprehensive duplicate elimination while maintaining the performance benefits of selecting the optimal build side.
Data Source
AI summary
Attributes and semantics of duplicate insignificance that are inherent or inferred in a database language statement are detected. Also, a join operation that is inherent or inferred in the database language statement is detected and examined for join semantics. The join semantics specifies or refers to a driving table to be subjected to a hash join operation that may populate one or more hash buckets. The optimizer and the execution layers may use cost estimation or heuristics to assign the left and right table roles to the tables involved in the join. The hash join operation removes left table duplicates during population of the hash buckets, resulting in full or partial duplicate elimination that occurs during the hash join operation.