Inline View Subsumption for Redundant Join Elimination
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems inefficiently handle queries with multiple inline views or subqueries accessing the same tables and using the same join conditions, leading to redundant table accesses and join evaluations.
Innovation Solution
The technique of subsumption replaces multiple inline views or subqueries with a single subsuming view, factoring out common filter predicates and unifying predicates to reduce redundant accesses and evaluations, and optionally includes grouping and pre-computation of common aggregates for further optimization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If multiple inline views or subqueries are evaluated individually, then each view can be processed independently, but redundant table accesses and join evaluations occur leading to inefficient query execution
Solution Approach 1:
The patent merges multiple inline views or subqueries that access the same tables with identical join conditions into a single unified view. This consolidation eliminates redundant table accesses and join evaluations by performing them once and reusing the results across all original views, directly resolving the efficiency problem while maintaining the logical structure of the query.
Solution Approach 2:
The patent performs preliminary evaluation of common table accesses and join operations before they are needed by individual inline views. By pre-computing the joined results and storing them in a temporary structure, the system avoids repeating the same expensive join operations multiple times, thereby eliminating redundancy and improving overall query performance.
2Productivity
If a single subsuming view replaces multiple inline views, then redundant accesses are reduced, but query structure complexity increases
Solution Approach 1:
The patent introduces a subsuming view as an intermediary structure that sits between the original multiple inline views and the underlying base tables. This intermediary consolidates common join logic and table accesses, simplifying the overall query structure while maintaining the ability to support all original view requirements. The subsuming view acts as a mediator that reduces complexity by centralizing redundant operations.
3Productivity
If grouping and pre-computation are applied to common aggregates, then additional performance gains are achieved, but computation overhead increases
Solution Approach 1:
The patent applies preliminary grouping and pre-computation to common aggregates identified across multiple inline views. By detecting aggregate functions that operate on the same grouped columns, the system pre-computes these aggregates once and stores the results, avoiding repeated computation in each individual view. This preliminary action significantly reduces aggregation overhead while the system intelligently identifies only the common aggregates suitable for pre-computation.
Data Source
AI summary
Techniques for subsumption of inline views and subqueries in a query are described. An optimization technique of subsumption is enabled by inline views having identical tables and identical join conditions and having aggregation functions but no group-by clauses. When subsumption takes place, a single query block replaces the inline views (or subqueries) with a single inline view query block. Subsumption reduces multiple access to the same table and multiple evaluations of the same join conditions required to evaluate the query. The single query block includes factored out filter predicates and unified predicates that originate from the subsumed inline views (or subqueries). Based on similarities among the aggregation functions and filter predicates in the subsumed inline views, pre-computation of common aggregates may be performed in a new group-by view in the subsuming view.