Dynamic Access Paths Using Real-Time Statistics and Machine Learning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational databases face inefficiencies in generating access paths due to outdated statistics and lack of support for volatile tables, leading to suboptimal data retrieval processes.
Innovation Solution
A method is introduced that uses real-time statistics estimates and machine learning to dynamically generate efficient access paths for SQL commands, even in volatile table scenarios, by training a model to map real-time database changes to optimal access paths.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If periodic statistics are used to generate access paths, then the system is simple to implement, but the access paths become outdated and inefficient when database changes occur
Solution Approach 1:
The patent transforms the static periodic statistics approach into a dynamic real-time statistics generation system. The access path generator continuously monitors database changes and updates statistics on-demand, allowing the system to adapt to changing database states without following a fixed periodic schedule, thus resolving the contradiction between implementation simplicity and retrieval efficiency.
Solution Approach 2:
The system implements feedback mechanisms where the access path generator monitors database change logs and uses this information to trigger real-time statistics generation. This feedback loop ensures that statistics are updated when actually needed rather than on a fixed schedule, maintaining efficiency while managing system complexity.
2Productivity
If real-time statistics generation is implemented, then data retrieval efficiency improves, but system complexity and resource consumption increase
Solution Approach 1:
The system performs preliminary actions by monitoring and logging database changes as they occur. The change log captures insert, update, and delete operations in real-time, preparing the data needed for statistics generation before it is actually required for access path optimization, thus reducing the complexity of on-demand real-time analysis.
Solution Approach 2:
The patent introduces an intermediary change log component that sits between the database operations and the statistics generation process. This intermediary captures and stores change information, allowing the access path generator to retrieve pre-processed data rather than directly monitoring raw database operations, thereby reducing system complexity.
3Adaptability or versatility
If traditional statistics methods are used, then volatile tables cannot be supported, but implementing real-time monitoring increases resource usage
Solution Approach 1:
The system implements self-service by having the volatile table operations automatically logged to the change log without requiring external monitoring infrastructure. The volatile table itself generates the change information that feeds into the statistics generation process, eliminating the need for additional monitoring resources while enabling support for volatile tables.
Data Source
AI summary
Embodiments are disclosed for a method for dynamic access paths. The method includes generating real-time statistics (RTS) estimates based on a log of a database. Further, the method includes generating access paths based on a structured query language command and the RTS estimates. The method also includes training a machine learning model to map the RTS estimates to the access paths.