Subquery Removal via Join-Based Containment
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies when handling queries with multiple subqueries that access the same tables and have the same join conditions, leading to redundant table accesses and join evaluations.
Innovation Solution
The implementation of join and set operation-based containment criteria allows for the removal of redundant subqueries by transforming queries to establish semantic equivalence, reducing redundant table accesses and join operations through subquery unnesting and inline views.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If multiple subqueries are evaluated individually, then each subquery can be processed independently, but redundant table accesses and join evaluations occur
Solution Approach 1:
The patent merges multiple subqueries that access the same tables with identical join conditions into a single coalesced subquery. This combining eliminates redundant table accesses and join evaluations while preserving the independent processing benefit through systematic subquery analysis and merging rules that identify equivalent subqueries based on their access patterns and join conditions.
Solution Approach 2:
The patent creates a universal coalesced subquery that serves multiple original subqueries simultaneously. This single subquery structure handles the functionality of multiple individual subqueries by establishing containment relationships and using set operations (UNION, INTERSECT, MINUS) to represent the logical OR relationships between the original subqueries, thereby improving efficiency while maintaining operational independence.
2Productivity
If subqueries are coalesced when one includes additional filter or correlation conditions, then redundant operations are reduced, but the coalescing capability is limited
Solution Approach 1:
The patent inverts the traditional coalescing approach by not requiring one subquery to contain additional conditions. Instead, it identifies subqueries with identical join conditions and table accesses, then uses set operations to represent the logical relationships. This inversion expands coalescing applicability to cases where subqueries have different filter or correlation conditions, as long as their base access patterns are equivalent.
Solution Approach 2:
The patent changes the parameters for determining coalescing eligibility from requiring one subquery to contain another to using set operation semantics. By representing subquery relationships through UNION, INTERSECT, and MINUS operations, the system can coalesce subqueries with different filter and correlation conditions, significantly expanding the versatility of subquery optimization while maintaining productivity gains.
3Productivity
If subquery containment is established using traditional methods, then some redundant operations are eliminated, but containment detection is limited to specific cases
Solution Approach 1:
The patent introduces set operation semantics as an intermediary framework for detecting and establishing subquery containment. Instead of directly comparing complex subquery structures, the system uses UNION, INTERSECT, and MINUS operations as mediators to represent containment relationships. This intermediary approach simplifies containment detection by providing a standardized mathematical framework that handles various subquery configurations uniformly, reducing detection difficulty while improving productivity.
4Productivity
If queries are transformed using subquery unnesting and inline views, then execution efficiency improves, but query complexity increases
Solution Approach 1:
The patent applies segmentation by breaking down the coalesced subquery into distinct components: the common table access portion, the set operation layer, and the individual subquery logic. This segmentation allows the query optimizer to handle each segment separately, managing complexity through structured decomposition while achieving execution efficiency through the unified coalesced structure. The segmented approach makes the transformation process more manageable and the resulting query structure more interpretable.
Data Source
AI summary
Techniques are described herein for subquery removal given two set operation-based subqueries in a query, where one subquery contains the result of the other. The described optimization technique of subquery removal is enabled by join and set operation-based containment of the set operation-based subqueries where semantic equivalence can be established for a given pair of set operation-based subqueries when some table(s)—with associated join condition(s), correlation condition(s), and/or filter predicate(s)—in one subquery are not considered. Subquery removal reduces multiple access to the same table and multiple evaluations of the same join conditions required to evaluate the query. When a subquery is removed from a disjunction, this may lead to other optimizations such as subquery unnesting, e.g., when the original query configuration would not permit query unnesting and the rewritten query (with one or more removed subqueries) permits unnesting.