Query Optimization via Semi-Join Predicate Unnesting
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Queries with correlated EXISTS-equivalent predicates lead to sub-optimal execution plans due to the evaluation of Cartesian products, resulting in poor performance, especially when indexes are absent on local columns.
Innovation Solution
Transforming the query representation by unnesting predicate conditions based on subquery results, using semi-join or anti-join operators to apply predicate conditions separately from the join operation, thereby reducing the need for Cartesian product computations and optimizing join operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If correlated EXISTS-equivalent predicates are evaluated using traditional query processing methods, then the query structure is preserved and evaluated in a predictable order, but Cartesian product computations are performed leading to poor performance and excessive computational resources
Solution Approach 1:
The patent extracts the subquery evaluation from the traditional correlated predicate processing flow. By transforming EXISTS-equivalent predicates into semi-join or anti-join operations, the subquery is evaluated independently and its results are used to filter the outer query, avoiding the Cartesian product computation that would otherwise occur during correlated evaluation
Solution Approach 2:
The patent changes the evaluation parameter from correlated row-by-row processing to set-based join operations. By converting the predicate evaluation from a correlated subquery approach to semi-join/anti-join operations, the computation transitions from evaluating predicates on each row combination to performing efficient set-based operations that avoid generating the full Cartesian product
2Reliability
If traditional query processing evaluates correlated subqueries for each row of the outer query, then the query semantics are preserved, but the execution time increases significantly due to repeated subquery evaluations
Solution Approach 1:
The patent performs preliminary evaluation of the subquery before applying it to the outer query. By evaluating the subquery once and materializing its results in a semi-join or anti-join operation, the system avoids repeatedly executing the same subquery for each row of the outer query, thereby preserving semantics while significantly reducing execution time
Solution Approach 2:
The patent merges the subquery evaluation with the join operation by transforming the correlated predicate into a semi-join or anti-join. This combines what would be separate operations (subquery evaluation followed by predicate application) into a single integrated join operation that achieves the same semantic result more efficiently
3Measurement precision
If Cartesian product computations are performed for correlated predicates, then all possible row combinations are considered ensuring complete evaluation, but the memory requirements and processing overhead increase dramatically
Solution Approach 1:
The patent applies partial action by evaluating the subquery only for the necessary set of rows rather than forming the complete Cartesian product. Semi-join and anti-join operations compute only the relevant intersections or differences between datasets, avoiding the generation and storage of unnecessary row combinations while still ensuring complete and accurate evaluation
Data Source
AI summary
A method, apparatus, and stored instructions are provided for transforming a query representation by unnesting a predicate condition that is based on whether or not a result exists for a subquery of the predicate condition. An initial query representation is received. The initial query representation represents an initial query that includes an EXISTS-equivalent predicate or a NOT-EXISTS-equivalent predicate and at least one other predicate in a disjunction. The initial query representation is transformed into a semantically equivalent transformed query representation that represents a transformed query. The transformed query includes, instead of the EXISTS-equivalent predicate or a NOT-EXISTS-equivalent predicate, a join operator that references the data object. The transformed query representation, when used for execution, causes the at least one other predicate to be applied separately from a join operation caused by the join operator such that execution of the initial representation is semantically equivalent to execution of the transformed representation.