Cost-Based Semi-Join Rewrite for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing semi-join operations in relational database management systems are inefficient when the inner table is much larger than the outer table, as the cost of pre-join sorting and duplicate elimination dominates the query performance, leading to suboptimal performance in queries like those in TPC-H and TPC-DS benchmarks.
Innovation Solution
A cost-based rewrite of semi-join operations is implemented, where inclusion joins are rewritten as inner joins with post-join sorting and duplicate elimination, and exclusion joins are rewritten as left outer joins with a post-join condition on the inner table's join column being null, to avoid spooling and sorting of the inner table, thereby reducing query execution costs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If pre-join sorting and duplicate elimination is performed on the inner table, then the cardinality of the inner table is reduced, but the execution cost increases significantly when the inner table is larger than the outer table
Solution Approach 1:
The patent inverts the conventional semi-join execution order by performing the join operation first and then eliminating duplicates post-join. Instead of sorting and removing duplicates from the inner table before the join (pre-join), the system performs the join between outer and inner tables first, then applies duplicate elimination to the join results. This inversion is particularly effective when the outer table is small, as the number of duplicates to eliminate post-join is limited by the size of the outer table, making the overall execution more efficient than pre-join sorting of a large inner table.
2Reliability
If pre-join sorting is performed to remove duplicates from the inner table, then duplicate elimination is achieved, but the cost of sorting dominates the join cost when the inner table is large
Solution Approach 1:
The patent applies the inversion principle by switching from pre-join duplicate elimination to post-join duplicate elimination. The system performs the join operation first between the outer and inner tables, then eliminates duplicates from the join results. This approach maintains accurate duplicate elimination (reliability) while avoiding the expensive pre-join sorting operation that dominates execution time when the inner table is large, thereby significantly improving query execution efficiency.
3Ease of operation
If the conventional semi-join execution plan is used, then the join operation is performed, but the overall query performance is suboptimal when the outer table is small and the join result is much smaller than the inner table
Solution Approach 1:
The patent inverts the conventional execution approach by performing the join first and then eliminating duplicates post-join, rather than eliminating duplicates before the join. When the outer table is small and the join result is much smaller than the inner table, this inversion ensures that duplicate elimination operates on a smaller result set, reducing the overall execution cost and significantly improving query performance while maintaining operational simplicity.
Data Source
AI summary
A method, apparatus, and computer program product for executing a relational database management system (RDBMS) in a computer system, wherein the RDBMS manages a relational database comprised of one or more tables storing data. The RDBMS executes a query with a semi-join operation comprising an inclusion join and/or an exclusion join performed against at least an outer table and an inner table, wherein the inclusion join returns a row from the outer table when there is a match with a row in the inner table, and the exclusion join returns a row from the outer table when there is no match with a row in the inner table. The RDBMS performs a rewrite of the query to avoid spooling and/or sorting of the inner table, when the inner table is larger than the outer table and a cost after the rewrite is lower than before the rewrite.