Database Histogram Forecasting via Historical Data Mining
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems require time-consuming and resource-intensive partial table partition scans to gather statistical metadata, which delays the execution of optimal queries and data loading processes.
Innovation Solution
A method and system for forecasting database table histograms using historical statistical histograms and database table statistics, employing frequency-based and height-balanced histogram forecasting techniques to predict future statistical values, thereby reducing the need for full or partial table scans and accelerating query optimization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If partial table partition scans are performed to gather statistical metadata, then accurate database optimization is achieved, but time consumption and hardware resource usage increase significantly
Solution Approach 1:
The system performs preliminary actions by gathering and storing statistical histograms during data loading operations. When queries are subsequently executed, the optimizer can utilize these pre-gathered statistics immediately without requiring additional table scans, thus resolving the contradiction between measurement precision and time loss.
Solution Approach 2:
The system dynamically determines whether to perform full scans, partial scans, or use pre-gathered statistics based on the specific query requirements and data characteristics. This dynamic approach allows the system to optimize for speed when pre-gathered statistics suffice, and for accuracy when full scans are necessary, thereby balancing measurement precision against time consumption.
2Loss of information
If full table scans are performed to gather complete statistics, then comprehensive statistical information is obtained, but hardware resource consumption and processing time increase
Solution Approach 1:
The system segments the table into multiple partitions and performs partial scans on only the necessary partitions to gather statistical information. This segmentation allows the system to obtain comprehensive statistical information for queried partitions while avoiding the need to scan the entire table, thus reducing hardware resource consumption while maintaining information completeness.
Solution Approach 2:
The system applies partial action by gathering statistics on only the portions of the table that are relevant to current or anticipated queries, rather than performing exhaustive full table scans. This approach obtains sufficient statistical information for optimization purposes without the excessive resource consumption of complete scans.
3Reliability
If statistics gathering is performed in serialized manner after data loading, then accurate statistics are available for optimization, but query execution is delayed
Solution Approach 1:
The system performs the statistics gathering action preliminarily during the data loading process itself, rather than serially after loading completes. This preliminary action ensures that accurate statistics are available immediately when queries are executed, maintaining optimization reliability while eliminating the delay that would otherwise occur between data loading and query execution.
Solution Approach 2:
The system maintains continuity of useful action by overlapping the statistics gathering process with the data loading process. Instead of having a gap where data is loaded then statistics are gathered separately, both operations proceed in an integrated manner, ensuring that statistics are continuously available for optimization without interrupting query execution flow.
Data Source
AI summary
A method and system for forecasting a histogram in a database system is provided. The method includes determining that database table statistics and historical statistical histograms associated with specified subject matter have been previously retrieved. The database table statistics and historical statistical histograms are retrieved and determined to be frequency based histograms. Historical target values associated with the historical statistical histograms are identified and new target values associated with the historical target values are identified. A value identifying a number of occurrences for identified target values comprising the new target values and the historical target values is forecast and database table histograms comprising the identified target values are stored.