Database Outer Join Optimization via Null-Side Elimination
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Left and right outer joins in database management systems are computationally expensive to process, necessitating improved optimization techniques to enhance query processing efficiency.
Innovation Solution
The optimization of left and right outer joins is achieved by evaluating specific conditions, such as no expression from the null supplying side being used outside the join, the requirement for distinct values from the preserved side, and the null supplying side returning at most one row for each row from the preserved side, allowing for the computation of the result set by processing only the preserved side.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If left and right outer joins are processed using standard computation methods, then complete result sets including null values are obtained, but computational cost and processing time increase significantly
Solution Approach 1:
The patent extracts and evaluates specific conditions (Condition 1, 2, and 3) from the outer join processing to determine when optimization is applicable. By extracting these verification steps, the system can identify cases where the null supplying side can be eliminated, thus reducing computational overhead while maintaining result correctness in applicable scenarios
Solution Approach 2:
The patent changes the processing parameter by switching from full outer join computation to preserved-side-only computation when conditions are satisfied. This parameter change in the computation approach (from computing both sides to computing only the preserved side) directly reduces processing time while maintaining result accuracy for optimized cases
2Measurement precision
If the null supplying side is fully computed in outer joins, then accurate result sets are produced, but computational resources and processing complexity increase
Solution Approach 1:
The patent applies partial action by computing only the preserved side of the outer join when conditions are satisfied, rather than computing both the preserved side and the null supplying side. This partial computation approach maintains accuracy for the required result set while reducing unnecessary computational complexity in the null supplying side
3Productivity
If outer join optimization is applied by computing only the preserved side, then query execution efficiency improves, but applicability is limited to specific condition scenarios
Solution Approach 1:
The patent implements a dynamic optimization approach where the system evaluates conditions at runtime and adapts the computation strategy accordingly. When conditions are satisfied, the optimized preserved-side-only computation is applied; when not satisfied, the system falls back to standard outer join processing, thus maintaining adaptability across different query scenarios while achieving high productivity in applicable cases
Data Source
AI summary
Techniques for left outer join (LOJ) and right outer join (ROJ) optimization are described herein. Each LOJ/ROJ is an expression that references a preserved side and a null supplying side. If Condition 1 is true, and either Condition 2 or Condition 3 is true, then the LOJ/ROJ is evaluated by evaluating the expression referencing only the preserved side, and not the null supplying side. Conditions 1, 2 and 3 are defined as follows: Condition 1: An expression from the null supplying side is not used outside of the LOJ/ROJ. Condition 2: The LOJ/ROJ is required to return only distinct values from the preserved side. Condition 3: The null supplying side returns at most one row for each row returned by the preserved side.