Self-adjusting Database Query Optimizer for Dynamic Plan Switching
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database query optimizers rely on statistics that may not reflect real-time conditions, leading to inefficient query-execution plans due to inaccurate estimates, resulting in poor system performance or failure to return answer sets.
Innovation Solution
A self-adjusting database-management system (DBMS) that initiates execution with an initial query-execution plan and dynamically switches to alternative plans based on actual resource usage, comparing actual and expected results at intermediate checkpoints to adjust the query-execution path and optimize resource allocation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If cost-based optimizer uses statistics to estimate query execution cost, then query execution plan can be optimized, but inaccurate statistics lead to decision-making errors and inefficient query-execution plans
Solution Approach 1:
The patent implements feedback by monitoring actual query execution results and comparing them against expected results based on statistics. When discrepancies exceed thresholds, the system adjusts the query execution plan dynamically. This closed-loop feedback mechanism resolves the contradiction by continuously improving plan accuracy while maintaining high execution efficiency.
Solution Approach 2:
The patent introduces dynamic adjustment of query execution plans during runtime based on actual execution conditions. The system transitions from static plan selection to dynamic plan modification, allowing the optimizer to adapt to real-time data conditions and resolve the contradiction between planned efficiency and actual reliability.
2Device complexity
If rule-based optimizer uses predefined rules for query execution, then system complexity is reduced, but inaccurate statistics still lead to poorly optimized query-execution plans
Solution Approach 1:
The patent enables the optimizer to self-adjust by automatically monitoring execution results and modifying plans without external intervention. This self-service capability allows the simplified rule-based system to improve its own performance, resolving the contradiction between low complexity and high execution efficiency.
3Reliability
If DBMS switches to alternative query-execution plans based on actual results, then query execution reliability is improved, but system complexity increases due to dynamic plan adjustment
Solution Approach 1:
The patent segments the query execution process into distinct phases with predetermined checkpoints. This segmentation allows the system to manage complexity by handling plan adjustment at specific, manageable points rather than continuously, thereby improving reliability while controlling system complexity.
Data Source
AI summary
A database-management system (DBMS) initiates execution of an incoming database query according to an initial query-execution plan that identifies an expected path for execution. Then, at some point after execution of the query has begun, the DBMS concludes that execution has not proceeded along the expected path and, in response, chooses an alternative query-execution plan for continued execution of the query.