Redundant Join Elimination via Subsumption in SQL Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query optimization techniques in relational database management systems fail to effectively eliminate redundant joins when the join conditions do not involve primary keys, limiting efficiency in query processing.
Innovation Solution
A method is introduced to optimize queries by evaluating join predicates between sub-expressions, determining subsumption relationships, and reforming the query to eliminate redundant joins and sub-queries, utilizing the Query Graph Model (QGM) to identify and remove unnecessary join predicates and quantifiers, thereby improving query efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If existing query optimization techniques are used, then query processing can be performed, but redundant joins cannot be eliminated when join conditions do not involve primary keys, resulting in reduced query efficiency
Solution Approach 1:
The patent extracts and removes redundant join operations from the query execution plan by identifying join predicates that reference the same base table and determining that one join is subsumed by another. This extraction principle directly eliminates unnecessary computational steps while preserving the essential query logic, thereby improving query processing efficiency without adding system complexity.
Solution Approach 2:
The patent performs preliminary analysis of join predicates during the query optimization phase, before query execution begins. By evaluating subsumption relationships between joins in advance and restructuring the query plan accordingly, the system prepares an optimized execution path that eliminates redundant operations upfront, preventing wasted computational effort during actual query processing.
2Productivity
If redundant joins are eliminated using subsumption, then query processing efficiency is improved, but the complexity of the query optimization process increases
Solution Approach 1:
The patent introduces an intermediary subsumption evaluation mechanism that acts as a mediator between the query parser and the query executor. This intermediary layer analyzes join predicates, determines subsumption relationships, and transforms the query plan accordingly. By inserting this intelligent intermediary step, the system achieves sophisticated redundancy elimination without requiring fundamental changes to the entire query processing architecture.
Solution Approach 2:
The query optimization process performs self-service by automatically analyzing its own query plans and identifying redundant joins through subsumption evaluation. The system uses its existing resources and capabilities to optimize its own operation, eliminating the need for external intervention or complex manual optimization procedures. This self-optimizing capability improves efficiency while keeping the added complexity manageable.
Data Source
AI summary
A method, computer readable medium, and system for optimizing a query in a relational database processing system is disclosed. The present invention relates to a query rewrite optimization method for eliminating a redundant join and equivalent subquery in an SQL query before generation and selection of the optimal query execution plan. The method of the present invention includes evaluating the query to identify a join predicate joining a sub-expression of the query to itself, and determining whether a row set producible from a first set of references of the query to the sub-expression is subsumed by a row set producible from a second set of references of the query to the sub-expression. Based on such evaluation and determination, the query may be reformed to eliminate the join predicate and the second quantifier. A further determination of the removability of the second quantifier may be required such as by evaluating a cardinality constraint when query output cardinality is material.