Hash Join Query Processing for Database Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database management systems face inefficiencies when processing queries that require tabular data, as they often need to disassemble tabular data into scalar values, leading to longer SQL statements and increased processing time for determining associated entries across tables.
Innovation Solution
The system employs a hash join operation to efficiently determine entries of a target table associated with entries of another table by serializing and deserializing tabular data, using a hash function to map concatenated value identifiers to indices, thereby reducing memory usage and processing time.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If tabular data is disassembled into scalar values and passed as query parameters, then the query can be processed by conventional SQL engines, but the processing time increases and memory usage increases due to m*n search requirements
Solution Approach 1:
The patent segments the query processing into two distinct phases: a build phase where a hash table is constructed from the first table's data, and a probe phase where the second table is searched using hash values. This segmentation eliminates the need for conventional m*n nested loop searches, reducing processing time from O(m*n) to O(m+n) while maintaining full query capability.
Solution Approach 2:
The patent replaces the mechanical sequential search mechanism (m*n nested loops) with a hash-based lookup mechanism. By substituting the conventional SQL engine's row-by-row comparison approach with hash table indexing, the system achieves dramatically faster processing while handling the same tabular data operations.
2Ease of operation
If tabular data is disassembled into scalar values, then the data can be passed to SQL engines, but the SQL statements become longer and more complex
Solution Approach 1:
The patent introduces an intermediary hash table structure that sits between the two tables being joined. Instead of directly comparing all rows from table1 with all rows from table2 (requiring complex nested queries), the hash table serves as a mediator that pre-organizes table1 data by join key values, simplifying the subsequent lookup operation to straightforward hash probes.
Solution Approach 2:
The patent performs preliminary action by constructing the hash table from the first table before processing the second table. This pre-processing step organizes the data in an optimal structure for rapid lookup, eliminating the need for complex runtime comparisons and simplifying the overall query execution plan.
3Reliability
If conventional m*n search is used to determine associated entries across tables, then all possible combinations are checked, but the processing time and computational resources increase significantly
Solution Approach 1:
The patent replaces the exhaustive mechanical comparison system (m*n nested loops checking all combinations) with a hash-based indexing system. The hash table stores pre-computed hash values of join keys from table1, allowing the probe phase to rapidly identify matching rows from table2 without comparing every possible pair, thus maintaining 100% association accuracy while dramatically improving execution speed.
Solution Approach 2:
The patent segments the data association task into two efficient phases: building a hash-indexed structure from table1 during the build phase, and performing targeted lookups from table2 during the probe phase. This segmentation eliminates the need for exhaustive comparison while ensuring all valid associations are found, achieving both reliability and productivity.
Data Source
AI summary
A system includes determination of a value identifier for each value of a first table, based on a data dictionary of a second table, generation of a hash function, the hash function mapping, for each row of the first table, one or more value identifiers of one or more columns of the row to a pointer to the row, application of the hash function to one or more value identifiers of a row of a second table, determination of whether the output of the hash function is a valid hash, and, if it is determined that the output of the hash function is a valid hash, determination that the row of the second table is associated with the first table.