Distance-Based Logical Exploration in Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database systems face challenges in generating optimal execution plans due to the exponential growth of possible logical orderings with the number of tables referenced, making it computationally infeasible to explore the entire plan search space, especially for complex queries.
Innovation Solution
A method involving the generation of initial logical representations of queries, followed by a bounded exploration process using transformation rules within a maximum allowable transformation distance, and selecting an execution plan based on cost considerations, allowing for a staged exploration of the plan search space.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Manufacturing precision
If the query optimizer explores the entire plan search space to find optimal execution plans, then the quality of execution plans improves, but the compilation time becomes computationally infeasible
Solution Approach 1:
The patent segments the plan search space by introducing a transformation distance metric, dividing the exploration into bounded regions around initial logical representations. This allows the optimizer to focus on promising areas without exhaustively searching the entire space, thus improving execution plan quality within feasible compilation time.
Solution Approach 2:
The patent applies partial exploration by limiting the search to transformations within a maximum allowable distance from initial logical representations. This partial action approach generates sufficiently good execution plans without the excessive computation required for complete exploration, resolving the contradiction between plan quality and compilation time.
2Adaptability or versatility
If the number of tables referenced by a query increases, then the complexity of the query increases, but the number of possible logical orderings grows extremely fast making optimization infeasible
Solution Approach 1:
The patent performs preliminary action by generating initial logical representations before exploration. This preliminary step structures the search space in advance, allowing the optimizer to handle complex queries with many tables more efficiently by starting from meaningful initial points rather than exploring all permutations.
Solution Approach 2:
The patent changes the parameter of search space exploration by introducing transformation distance as a limiting factor. This parameter change allows the system to handle increased query complexity by bounding the exploration depth, preventing the exponential growth of logical orderings from becoming infeasible.
3Reliability
If the query optimizer performs exhaustive exploration of all execution plans, then the reliability of selecting the optimal plan improves, but the productivity of the system decreases
Solution Approach 1:
The patent incorporates feedback mechanisms where the optimizer evaluates execution plans generated within the bounded transformation distance and uses cost information to guide further exploration. This feedback allows reliable selection of optimal plans from the explored subset while maintaining system productivity by avoiding exhaustive search.
Data Source
AI summary
Systems and methods are described that generate an execution plan for a query in a relational database system. The systems and methods generate the execution plan by generating one or more initial logical representations of the query, performing an exploration process around each of the one or more initial logical representations of the query, the performing of the exploration process around a particular initial logical representation of the query comprising applying transformation rules to generate one or more additional logical representations of the query that are logically equivalent to the particular initial logical representation of the query and that are within a maximum allowable transformation distance of the particular initial logical representation of the query, generating one or more execution plans for each initial logical representation of the query and each additional logical representation of the query, and selecting an execution plan from among the generated execution plans.