Database Date Statistics Extension via Extrapolation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Large databases face challenges in maintaining up-to-date statistics, particularly for date columns, which require frequent updates, leading to inefficiencies and delays in optimization processes, as existing methods are either cumbersome or require changes to the optimizer.
Innovation Solution
A method for extending database date statistics by estimating current statistics from prior data, allowing for customizable extrapolation methods, enabling efficient and timely updates without overloading the database or requiring optimizer changes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If statistics gathering services are used to collect up-to-date statistics for date columns, then the accuracy of optimization plans is improved, but the database becomes overloaded and processing is delayed
Solution Approach 1:
The patent applies preliminary action by pre-calculating and storing statistics for non-date columns before they are needed for optimization. When a query is processed, the optimizer can immediately use these pre-computed statistics without waiting for real-time calculation, thus improving processing speed while maintaining accuracy.
Solution Approach 2:
The patent extracts the statistics gathering function into a separate, optimized process that runs independently from the main query processing. The statistics gathering service collects statistics asynchronously and stores them in a cache, allowing the optimizer to retrieve pre-computed statistics without the gathering process interfering with query execution, thereby resolving the conflict between accuracy and processing speed.
2Reliability
If full statistics gathering is performed frequently to maintain current statistics, then the reliability of optimization plans is improved, but the resources consumed and time required increase significantly
Solution Approach 1:
The patent implements periodic action by having the statistics gathering service operate at scheduled intervals rather than continuously. Statistics are collected periodically and stored for reuse, allowing the system to maintain reliable optimization plans while avoiding the overhead of continuous full statistics gathering. This periodic approach significantly reduces the time and resources required while maintaining plan reliability.
Solution Approach 2:
The patent applies preliminary action by pre-computing statistics in advance and storing them in a cache structure. When optimization is needed, the pre-computed statistics are already available, eliminating the need for time-consuming real-time gathering. This preliminary computation ensures reliability while minimizing the time lost to statistics gathering operations.
3Measurement precision
If the optimizer waits for statistics gathering to complete before processing queries, then the accuracy of optimization plans is improved, but the processing delay increases
Solution Approach 1:
The patent introduces an intermediary statistics cache that sits between the statistics gathering service and the optimizer. The cache stores pre-computed statistics and provides them to the optimizer on demand, eliminating the need for the optimizer to wait for statistics gathering to complete. This intermediary layer decouples the statistics gathering process from query processing, ensuring accuracy while minimizing delays.
Solution Approach 2:
The patent applies preliminary action by having statistics pre-computed and stored in the cache before queries are processed. The optimizer can immediately retrieve these pre-computed statistics without waiting for the gathering process, thus maintaining accurate optimization plans while eliminating query processing delays.
Data Source
AI summary
Techniques for extending database date statistics are provided. A data dictionary houses existing date statistics for information housed in a column of a database table. The existing statistics housed in the data dictionary are used to project estimated statistics for the column for a target date that is not yet represented in the data dictionary and the data dictionary is updated with the estimated statistics.