Shared Translation Tables for Faster Database Semi-Joins
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database query execution methods, particularly hash joins, are resource-intensive and inefficient due to the costly scanning of values in multiple tables, leading to performance bottlenecks and increased processing times.
Innovation Solution
Implementing a method that optimizes query execution by detecting a join between tables, translating value identifiers to form a shared translation table, building a hash table using these identifiers, performing a table scan semi-join, and outputting matching rows, thereby reducing the need for extensive scanning and resource usage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional hash join methods are used to join two tables, then the join operation can be completed, but the resource consumption and processing time increase due to costly scanning of values in multiple tables
Solution Approach 1:
The patent extracts only the necessary value identifiers from the first table to build a translation table, rather than scanning all values in both tables. This selective extraction reduces the amount of data that needs to be processed and compared, directly lowering resource consumption while maintaining join functionality.
Solution Approach 2:
The patent performs preliminary action by building a translation table that maps value identifiers from the first table to the second table before executing the main join operation. This pre-computed mapping structure enables faster lookups during the join, reducing the computational burden during actual query execution.
2Productivity
If traditional hash join methods are used to join two tables, then the join operation can be completed, but the processing time increases due to extensive scanning of values
Solution Approach 1:
The patent creates a copied and transformed version of value identifiers from the first table into a translation table structure that is optimized for the second table's value domain. This copied structure serves as an efficient lookup mechanism, eliminating the need for time-consuming direct comparisons between all pairs of values from both tables.
Solution Approach 2:
The patent changes the parameter representation by translating value identifiers from one table's domain to another table's domain. This parameter transformation creates a shared value identifier space that enables efficient matching and reduces the computational complexity of the join operation, directly reducing processing time.
3Use of energy by moving object
If value identifiers are translated to form a shared translation table, then resource consumption is reduced, but the complexity of the join operation increases
Solution Approach 1:
The patent introduces a translation table as an intermediary structure that bridges the value identifier domains of two tables. This intermediary contains pre-computed mappings that simplify the join operation by providing direct lookup paths, reducing the need for complex comparison logic while maintaining resource efficiency.
Data Source
AI summary
In some embodiments, there is provided performing a table scan semi-join on a plurality of second value second identifier of the second table by scanning a plurality of second value identifiers that have been filtered by the translated plurality of first value identifiers and reading one or more filtered second value identifiers of the second table, providing the one or more filtered second value identifiers of the second table as a probe into the hash table including the translated plurality of first value identifiers, and outputting one or more matching rows corresponding to matching value identifiers for matching values in the first table and the second table. Related systems, methods, and articles of manufacture are also disclosed.