Database Query Join Order Determination via Candidate Substitution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems face inefficiencies in determining an optimal join order for query processing, particularly when dealing with a large number of tables, which can lead to increased memory usage and processing time, and may result in poor query pre-processing performance or even system outage.
Innovation Solution
An efficient enumeration technique is applied by the optimizer engine to determine an optimal join order, where a relational expression tree is transformed into one with a multijoin operator, and candidate substitutes are generated and evaluated based on cardinality estimates to reduce the number of enumerations, thereby conserving resources.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If all possible join orders are enumerated to determine the optimal join order, then the query processing performance can be optimized, but the memory usage and processing time increase significantly
Solution Approach 1:
The patent segments the complete enumeration of all possible join orders into a limited set of candidate join orders generated through specific rules and transformations. Instead of evaluating n! possible join orders for n tables, the system generates a manageable subset of candidate join orders by applying transformations to an initial join order, thereby reducing memory consumption while still identifying optimal or near-optimal join sequences.
Solution Approach 2:
The patent applies partial enumeration by generating only the necessary candidate join orders rather than exhaustively enumerating all possible join orders. The system generates candidate join orders through targeted transformations (such as swapping adjacent tables, cyclic shifts) applied to an initial join order, performing just enough enumeration to find optimal solutions without the excessive computational overhead of complete enumeration.
2Productivity
If all possible join orders are enumerated to determine the optimal join order, then the query processing performance can be optimized, but the processing time increases significantly
Solution Approach 1:
The patent divides the time-consuming complete enumeration process into segmented candidate generation through efficient transformations. By generating candidate join orders through localized transformations (swapping adjacent tables, cyclic shifts) rather than evaluating all permutations, the system reduces pre-processing time while maintaining the ability to identify optimal join sequences.
Solution Approach 2:
The patent performs partial enumeration by generating only the candidate join orders necessary to find optimal solutions. The system applies a limited set of transformations to an initial join order to generate candidates, avoiding the excessive time required for complete enumeration of all possible join orders while still achieving optimal query performance.
3Quantity of substance
If a limited number of candidate join orders are evaluated, then the resource consumption is reduced, but the accuracy of finding the optimal join order may be compromised
Solution Approach 1:
The patent performs preliminary action by generating candidate join orders through systematic transformations that are guaranteed to include or approximate the optimal join order. The initial join order is transformed through predefined operations (adjacent swaps, cyclic shifts) that explore the join order space efficiently, ensuring that the optimal or near-optimal join sequence is captured among the candidates without exhaustive enumeration.
Solution Approach 2:
The patent incorporates feedback mechanisms where the system evaluates candidate join orders based on cost estimates and iteratively refines the selection. By using cost-based optimization and evaluating candidates in order of estimated cost, the system ensures high accuracy in finding optimal join orders while consuming minimal resources, as the feedback from cost evaluation guides the search toward the best solutions.
Data Source
AI summary
A technique includes identifying a plurality of substitute candidates for a given multiple join of tables. Each substitute candidate includes a candidate multiple join of tables, and the number of tables of each candidate multiple join is less than the number of tables of the given multiple join. A given substitute candidate is selected based on a cardinality of the candidate multiple join of the given substitute candidate; a join order for the given multiple join is determined based on the given substitute candidate; and the query is processed based on the determined join order.