Query Optimizer Dynamic Plan Selection via Cardinality Ranges
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database query optimization techniques often fail to adapt to changing conditions, leading to sub-optimal execution plans due to estimation errors and constant updates, especially in complex hybrid transactional analytical processing systems, where traditional caching strategies are not effective.
Innovation Solution
The query optimizer determines optimality ranges for intermediate result cardinalities, allowing for the selection of alternative query execution plans when actual cardinalities fall outside pre-calculated bounds, and uses parametric cost functions to model and compare plan costs, enabling efficient mid-query re-optimization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If traditional query caching strategies are used, then query execution speed is improved, but the system cannot adapt to changing conditions and constant updates, leading to sub-optimal execution plans
Solution Approach 1:
The patent implements dynamic query plan selection by determining optimality ranges for intermediate result cardinalities and selecting alternative plans when actual cardinalities fall outside pre-calculated bounds. This allows the system to adapt execution plans dynamically based on changing data conditions while maintaining caching benefits for stable scenarios.
Solution Approach 2:
The system uses parametric cost functions to model plan costs as functions of intermediate result cardinalities. By changing the parameter (cardinality) and comparing it against optimality range bounds, the system determines whether to use cached plans or re-optimization, enabling adaptation to changing conditions.
2Reliability
If query re-optimization is performed frequently to maintain optimality, then execution plan quality is improved, but re-optimization overhead increases
Solution Approach 1:
The patent performs preliminary determination of optimality ranges and alternative plan costs during the optimization phase. This preliminary action allows the system to quickly compare actual cardinalities against pre-calculated bounds during execution, avoiding frequent full re-optimizations and reducing overhead while maintaining plan quality.
Solution Approach 2:
The system uses feedback from actual intermediate result cardinalities to determine whether to switch from cached plans to alternative plans. By monitoring cardinality values against optimality ranges and using this feedback for plan selection, the system maintains optimality without unnecessary re-optimization cycles.
3Adaptability or versatility
If alternative query execution plans are pre-calculated and cached, then mid-query switching is enabled, but device complexity increases
Solution Approach 1:
The patent segments the query execution into distinct phases: optimization phase (where alternative plans and optimality ranges are pre-calculated), execution phase (where cardinalities are monitored and plans are selected), and switching phase (where alternatives are activated). This segmentation manages complexity by organizing complex operations into manageable, distinct stages.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
In some implementations, there is provided an apparatus having at least one data processor and at least one memory storing instructions which, when executed by the at least one data processor, cause executing at least a portion of a query execution plan; determining, at an edge including an intermediate result, a cardinality; proceeding with the execution of the query execution plan, when the determined cardinality is within an optimality range associated with the edge; and selecting an alternative query execution plan for execution, when the determined cardinality is outside the optimality range associated with the edge. Related systems and articles of manufacture are also provided.