Pseudo-join Predicate for Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current query optimization techniques are inefficient in handling complex join operations, particularly with multiple join predicates, leading to computationally expensive query executions that can take hours or days, consuming valuable resources and deterring customers from purchasing optimization products.
Innovation Solution
Transforming a query with a single-table outer join predicate into a semantically equivalent query with a multi-table predicate, allowing for more efficient execution plans and optimized data organization, such as using pseudo-join operators that reference multiple tables, enabling the preservation of sorted data or hash tables for improved join evaluations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a single-table join predicate is used in outer join operations, then the query can be executed with current optimization techniques, but the query execution becomes computationally expensive and time-consuming
Solution Approach 1:
The patent introduces a pseudo-join predicate as an intermediary construct that references multiple tables simultaneously. This pseudo-join predicate acts as a mediator between the outer join operation and the inner join predicates, allowing the query optimizer to rewrite and reorganize the join operations more efficiently. By using this intermediary structure, the system can preserve sorted data and hash tables across multiple join operations, significantly reducing execution time from hours to minutes.
Solution Approach 2:
The patent transforms the join predicate from a single-table reference to a multi-table reference, changing the fundamental parameter of how join conditions are evaluated. This parameter change enables the query to leverage existing sorted data and hash tables from previous join operations, rather than re-evaluating all conditions from scratch, thereby dramatically improving execution performance.
2Productivity
If multiple join predicates are used in complex join operations, then more comprehensive data joining is achieved, but computational resources are consumed excessively
Solution Approach 1:
The patent merges multiple join predicates into a single pseudo-join predicate that references multiple tables simultaneously. Instead of evaluating each join predicate separately and consuming computational resources for each, the system combines them into one unified structure that can leverage shared data organization (sorted data and hash tables), thereby reducing overall computational resource consumption while maintaining comprehensive data joining capabilities.
Solution Approach 2:
The pseudo-join predicate serves multiple functions simultaneously: it acts as a join condition, references multiple tables, and enables preservation of sorted data and hash tables across different join operations. This multi-functionality reduces the need for separate processing steps and minimizes computational resource consumption while achieving comprehensive data joining.
3Measurement precision
If join predicates are evaluated on a row-by-row basis, then accurate join results are produced, but the evaluation process becomes computationally expensive
Solution Approach 1:
The patent performs preliminary organization of data into sorted structures and hash tables before the actual join evaluation. By pre-sorting data and building hash tables from previous join operations, the system prepares the data structure in advance, allowing subsequent join predicate evaluations to proceed more efficiently without requiring excessive computational power for each individual row evaluation.
Data Source
AI summary
Methods, computing devices, and computer-readable media are provided for transforming an initial query with a single-table outer join predicate to a second, semantically equivalent query that includes a multi-table predicate instead of the single-table outer join predicate. Because the second query does not include the single-table outer join predicate, the second query may be available for further transformations or optimization techniques, may produce a more efficient or otherwise different execution plan, or may be executed more efficiently or differently. The single-table outer join predicate may be in a full outer join ON clause of the initial query or nested in a left outer-joined LATERAL VIEW of the initial query. The multi-table predicate may be in a WHERE clause of the second query. Unlike the initial query, the second query allows use of a temporary organization of data from multiple tables for evaluation of multiple rows of the left outer join.