Multi-Optimizer Database Query Planning Through Plan Translation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems face limitations in optimizing database queries due to the limitations of query optimizers, leading to suboptimal physical plans being generated.
Innovation Solution
A method and apparatus that utilize multiple query optimizers to generate and refine a skeleton plan, allowing for a more optimal physical plan by leveraging the capabilities of different query optimizers, such as MySQL and Orca, to enhance query optimization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If a single query optimizer is used, then the system is simple to operate, but the query optimization capability is limited
Solution Approach 1:
The patent combines multiple query optimizers (first query optimizer and second query optimizer) into a unified system that collaboratively optimizes database queries. The first query optimizer generates an initial physical plan, which is then refined by the second query optimizer to produce an optimized physical plan, thereby merging the capabilities of multiple optimizers to overcome individual limitations.
Solution Approach 2:
The patent introduces a plan merger as an intermediary component that receives physical plans from multiple query optimizers, merges them, and resolves conflicts. This mediator enables different optimizers to work together without direct interaction, facilitating complex query optimization while maintaining system modularity and manageability.
2Productivity
If multiple query optimizers are used, then the query optimization capability is improved, but the system complexity increases
Solution Approach 1:
The patent segments the query optimization process into distinct phases handled by different components: the first query optimizer handles initial query analysis and generates a baseline physical plan, while the second query optimizer focuses on refining and optimizing the plan. This segmentation allows each optimizer to specialize in specific tasks, improving overall throughput while managing complexity through clear division of labor.
Solution Approach 2:
The first query optimizer performs preliminary query analysis and generates an initial physical plan before the second query optimizer refines it. This preliminary action prepares the query in a standardized format, reducing the complexity burden on the second optimizer and enabling more focused optimization efforts that improve system throughput.
3Manufacturing precision
If a complex query is optimized by a single optimizer, then the process is straightforward, but the optimization quality is suboptimal
Solution Approach 1:
The patent replaces the single-optimizer mechanical system with a multi-optimizer system where the first query optimizer generates an initial physical plan and the second query optimizer refines it. This substitution enables more sophisticated optimization for complex queries by leveraging multiple optimization perspectives, improving physical plan quality while managing process complexity through structured collaboration.
Data Source
AI summary
There is provided a method and apparatus for optimizing a database query. Embodiments expand the scope of query optimization to two or more query optimizers. Therefore, a larger class of plan trees can be explored, and a more optimal (for example faster) physical plan may be chosen for execution. The query will continue to be executed by the “original”, “home” or “first” query execution engine, and therefore, a translation or conversion mechanism is be put into place that is able to convert the chosen physical plan into a format that is acceptable to the original optimizer.