Decorrelating Lateral Views for Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database query optimization is hindered by the computational expense of evaluating complex join predicates in lateral views, which limits the choice of join methods and access paths, particularly due to the reliance on computationally expensive nested-loop joins.
Innovation Solution
Transforming queries by removing correlated predicates from lateral views to create de-correlated views, allowing for a wider range of join methods and access paths, including hash joins and sort-merge joins, and moving predicates to the WHERE clause to preserve semantic equivalence.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If correlated predicates are evaluated in lateral views, then query correctness is ensured, but computational overhead increases and join method choices are limited
Solution Approach 1:
The patent extracts correlated predicates from the lateral view definition and moves them to the outer query's WHERE clause. This separation allows the lateral view to be evaluated independently without the computational burden of correlated subqueries, reducing overhead while preserving the original query's semantic meaning and correctness through the outer query's filter conditions.
2Reliability
If correlated predicates are evaluated in lateral views, then query semantics are preserved, but join method options are restricted to nested-loop joins
Solution Approach 1:
By extracting correlated predicates from the lateral view and placing them in the outer query's WHERE clause, the patent removes the restriction that forced nested-loop join evaluation. The lateral view can now be evaluated using more efficient join methods (hash join, sort-merge join) independently, while the outer query maintains the original semantic requirements through its filter conditions.
3Measurement precision
If complex join predicates are evaluated in lateral views, then query accuracy is maintained, but query execution performance deteriorates
Solution Approach 1:
The patent extracts complex join predicates from the lateral view definition and relocates them to the outer query's WHERE clause. This allows the lateral view to be evaluated once using efficient join methods without repeated predicate evaluation for each outer row, significantly improving execution performance while the outer query's WHERE clause ensures the original accuracy requirements are met.
Solution Approach 2:
By evaluating the lateral view independently before the outer query execution, the patent performs the computationally expensive join operation in advance. The extracted predicates are then applied as simple filters in the outer query, avoiding the need to re-evaluate complex conditions for each row combination and thereby improving overall query execution performance.
Data Source
AI summary
Methods, computer-readable media, and machines are provided for transforming a query by removing a correlated predicate from a lateral view in an initial query. A query processor may transform the lateral view into a regular view such that a transformed query with the regular view is semantically equivalent to the initial query. The transformed query may support a larger set of access paths, join methods, and join permutations, may qualify for other transformations or optimizations, or may otherwise be executed or optimized differently from the initial query. The lateral view may reference an aggregation function, and the query processor may add a GROUP BY operator to the transformed query to preserve, after the correlated predicate has been pulled out of the lateral view, an interaction that was present between the aggregation function and the correlated predicate when the correlated predicate was in the lateral view.