Recursive Query Optimizer Adaptive Execution Plans
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Optimizing the execution of recursive database queries is challenging due to varying cardinalities across iterations, where conventional static planning methods use a single execution plan for all iterations, leading to inefficiencies and potential interruptions with full feedback techniques.
Innovation Solution
A Recursive Query Optimizer (RQO) determines whether to use an execution plan based on the divergence between estimated and actual results, allowing for dynamic re-planning and adaptive optimization across iterations, using a divergence metric to decide when to re-evaluate and adjust the execution plan.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a single static execution plan is used for all iterations of a recursive query, then device complexity is reduced and ease of operation is improved, but query execution efficiency deteriorates due to varying cardinalities across iterations
Solution Approach 1:
The system transitions from static execution plans to dynamic execution plans that adapt to changing cardinalities across recursive query iterations. The divergence metric dynamically determines when re-planning is necessary, allowing the execution plan to evolve based on actual query results rather than remaining fixed throughout all iterations.
Solution Approach 2:
The system monitors changes in cardinality parameters across iterations and uses the divergence metric to detect when these parameter changes warrant execution plan re-planning. When the divergence between estimated and actual cardinalities exceeds a threshold, the system adjusts the execution plan parameters to optimize performance for the current iteration's characteristics.
2Productivity
If full feedback techniques are used to optimize each iteration, then query execution efficiency is improved, but system interruptions increase and operational complexity worsens
Solution Approach 1:
The system implements a selective feedback mechanism using the divergence metric to determine when full feedback processing is necessary. Instead of continuously re-planning after every iteration, the system monitors cardinality divergence and only triggers re-planning when the divergence exceeds a predetermined threshold, reducing unnecessary system interruptions while maintaining optimization where needed.
Solution Approach 2:
The system applies partial feedback processing by using the divergence metric to identify only those iterations where full re-planning is warranted. This selective approach applies optimization actions only when necessary (when divergence is high) rather than excessively re-planning every iteration, thereby reducing overhead while maintaining effectiveness.
Data Source
AI summary
Based on one or more predicated results (e.g., estimations of the actual results) associated with one or more iterations and/or sub-queries of an iterative or recursive database query, it can be determined whether to use an execution plan (e.g., a current execution plan used to execute one or more iterations) to execute one or more other iterations and/or sub-queries of the iterative or recursive database query. Consequently, use of the execution plan to execute the one or more other iterations and/or sub-queries of the iterative or recursive database query can be disallowed. As a result, the iterative or recursive database query can be executed more efficiently. The determination of whether to use an execution plan can, for example, be performed by determining whether an actual result obtained by executing one or more iterations differs or diverges more than a determined value from an estimated result of the same iteration(s).