AI Query Access Path Selection for Relational Database Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database queries, particularly for large relational databases, are inefficient and resource-intensive due to a lack of a priori knowledge about data location, leading to suboptimal access path selection and increased computing resource consumption.
Innovation Solution
Implementing a machine learning system, such as a neural network, to predict the costs of different access paths for database queries based on historical data and system changes, allowing for optimized query execution plans.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If traditional database query optimization is used without a priori knowledge, then queries can be executed on any database, but query execution time and resource consumption increase significantly
Solution Approach 1:
The system performs preliminary actions by pre-computing and storing cost estimates for different access paths in advance. When a query arrives, the optimizer retrieves these pre-computed costs instead of calculating them from scratch, enabling fast adaptation to new queries without a priori knowledge while significantly reducing execution time.
Solution Approach 2:
The system implements feedback mechanisms where actual query performance data is collected and used to update cost estimates for access paths. This feedback loop allows the optimizer to learn from past executions and improve future query optimization, balancing adaptability with efficient resource usage.
2Reliability
If broad database searches are performed without precise access path selection, then all possible results are found, but computing resources are wasted
Solution Approach 1:
The system applies partial action by selecting only the necessary portion of the database to search based on cost estimates of different access paths. Instead of performing exhaustive broad searches, the optimizer chooses access paths that will likely yield results with minimal resource consumption, achieving sufficient reliability without excessive computing resources.
Solution Approach 2:
The system changes parameters by dynamically adjusting cost estimates for different access paths based on current database state, query characteristics, and historical performance data. This allows the optimizer to adapt search strategies to match actual conditions, ensuring complete results are found while minimizing resource usage.
3Use of energy by moving object
If narrow database searches are performed with assumed data locations, then computing resources are saved, but results may be missed
Solution Approach 1:
The system uses feedback from actual query results and performance measurements to validate and adjust cost estimates for access paths. This ensures that narrow searches based on assumed data locations are continuously refined to maintain result accuracy while preserving computing resource efficiency.
Solution Approach 2:
The system performs preliminary analysis of query patterns and database characteristics to establish initial cost estimates for access paths. This preliminary action enables the optimizer to make informed decisions about search scope before execution, balancing resource efficiency with result accuracy by selecting appropriately narrow search paths.
4Productivity
If manual query optimization with a priori knowledge is used, then query performance is optimized, but the complexity of query crafting increases
Solution Approach 1:
The system implements self-service by automatically computing cost estimates and selecting optimal access paths without requiring manual intervention. The query optimizer autonomously analyzes query characteristics, evaluates different access paths, and generates optimized execution plans, maintaining high productivity while eliminating the complexity of manual query crafting.
Solution Approach 2:
The system replaces manual mechanical optimization processes with automated computational methods. Instead of requiring users to manually analyze and optimize queries based on a priori knowledge, the system uses automated cost estimation algorithms and performance data to substitute human expertise with machine-based optimization, reducing complexity while maintaining efficiency.
Data Source
AI summary
In some aspects, the disclosure is directed to methods and systems for access path selection optimization for relational database queries. Machine learning systems, such as neural networks, may be used to determine costs for each path. A neural network may be trained based on cost determinations from scans and indexes of the tree for an initial set of queries, and then may be used to predict costs for additional queries such that a path may be selected. The training data may be periodically refreshed or updated, or may be refreshed or updated responsive to changes in hardware or computing environment.