Database Query Access Path Evaluation Using ML Classifier
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for evaluating new access paths in Structured Query Language (SQL) are labor-intensive and costly, and traditional database optimizers may fail to select the most efficient access paths, leading to potential degradation in query performance after changes such as patches or version upgrades.
Innovation Solution
A method using a machine learning optimizer and a strong classifier to evaluate the risk of access path changes, employing a machine learning system to predict costs and select the most efficient access path, thereby enhancing the traditional database optimizer by automatically detecting and mitigating low-performing access paths.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a traditional cost-based database optimizer is used to select access paths, then the system operates with standard optimization logic, but it may fail to select the most efficient access paths leading to degraded query performance
Solution Approach 1:
A machine learning classifier is introduced as an intermediary component between the cost-based optimizer and access path selection. The classifier receives cost estimates from the traditional optimizer and predicts whether the selected access path will meet performance thresholds, thereby improving reliability without completely replacing the traditional optimization logic.
Solution Approach 2:
The optimizer system is segmented into multiple independent components: the traditional cost-based optimizer, the machine learning classifier, and the access path selector. This segmentation allows each component to specialize in specific tasks, with the classifier handling performance prediction and the traditional optimizer handling cost estimation, resolving the contradiction between reliability and complexity.
2Measurement precision
If manual comparison of access paths is performed to determine performance improvement, then accurate performance evaluation is achieved, but the process becomes labor-intensive and costly
Solution Approach 1:
The system performs self-service by automatically evaluating access path performance using the machine learning classifier, eliminating the need for manual comparison. The classifier autonomously predicts performance outcomes based on historical data and cost estimates, maintaining measurement precision while dramatically improving productivity.
Solution Approach 2:
Manual mechanical comparison processes are replaced with an automated machine learning system. The classifier uses computational algorithms to predict performance outcomes, substituting human labor with automated intelligence that maintains accuracy while improving evaluation efficiency and scalability.
3Adaptability or versatility
If access paths are changed after patches or version upgrades, then system updates are implemented, but query performance may degrade due to changed access path effectiveness
Solution Approach 1:
A feedback mechanism is implemented where the machine learning classifier continuously monitors access path performance after system updates. When performance degradation is detected, the system can provide feedback to adjust access path selections or trigger re-optimization, maintaining reliability while preserving adaptability to system changes.
Solution Approach 2:
The machine learning classifier is trained in advance on historical performance data before system updates occur. This preliminary action enables the classifier to predict performance outcomes of potential access path changes, allowing the system to prepare for and mitigate performance degradation before it occurs during patches or version upgrades.
Data Source
AI summary
An approach is provided for evaluating a performance of a query. A risk of selecting a low performance access path for a query is determined. The risk is determined to exceed a risk threshold. Based on the risk exceeding the risk threshold and using a machine learning optimizer, first costs of access paths for the query are determined. Using a cost-based database optimizer, second costs of the access paths are determined. Using a strong classifier operating on the first costs and the second costs, a final access path for the query is selected from the access paths.