Statview Recommendation System for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database query optimizers rely on inaccurate cardinality estimations for intermediate results, leading to inefficient query plans due to unrealistic assumptions about data correlations, which are not captured by basic statistics.
Innovation Solution
The method automatically recommends statistical views (statviews) based on cost/benefit metrics to improve the accuracy of cost estimates during query optimization, by analyzing candidate statviews, computing their benefit and cost, and selecting those that provide maximum benefit while minimizing construction costs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If basic statistics on base tables are used for cardinality estimation, then the estimation process is simple and fast, but the accuracy of cost estimates deteriorates significantly due to unrealistic assumptions about data correlations
Solution Approach 1:
The system performs preliminary analysis to identify candidate statistical views that would be beneficial for improving cardinality estimation accuracy. By pre-computing and recommending statviews before query execution, the system prepares accurate statistics in advance, allowing the query optimizer to use them without sacrificing optimization speed.
Solution Approach 2:
Statistical views serve as intermediaries between the base tables and the query optimizer. These statviews capture complex data correlations and intermediate result characteristics, providing the optimizer with accurate cardinality estimates without requiring the optimizer itself to perform complex correlation analysis.
2Measurement precision
If statistical views are constructed to improve cardinality estimation accuracy, then cost estimate accuracy improves, but the construction and maintenance cost increases
Solution Approach 1:
The system changes the parameter of interest from raw data to statistical characteristics by creating statviews that capture specific data correlations and intermediate result properties. This transformation allows the optimizer to work with pre-computed statistics rather than raw data, improving accuracy while managing complexity through targeted statistical collection.
Solution Approach 2:
The system automatically recommends and constructs statviews based on analysis of the workload and existing statistics, without requiring manual intervention. The automated recommendation process evaluates candidate statviews and selects those that provide the most benefit, reducing the operational complexity of maintaining statistical views.
3Measurement precision
If comprehensive statistics are collected on all possible intermediate results, then cardinality estimation accuracy is maximized, but the time and resources required for statistics collection and maintenance increases significantly
Solution Approach 1:
Instead of collecting statistics on all possible intermediate results, the system applies partial action by selectively collecting statistics only on those intermediate results that are most beneficial for query optimization. The recommendation system identifies and prioritizes critical statviews, collecting statistics only where they provide the most value, thus reducing time and resource overhead.
Solution Approach 2:
The system segments the statistics collection process by dividing intermediate results into different categories and collecting statistics selectively based on their importance. Rather than treating all intermediate results uniformly, the system segments them by their potential impact on query plan selection and collects statistics only on the most influential ones.
Data Source
AI summary
A workload to be handled by a database system can be identified. The workload can include at least one query that the database system is to handle. A set of at least one candidate statistical views (statviews) to be utilized when optimizing the workload can be enumerated. A benefit value and a cost value of the each of the enumerated candidate statistical views relative to the entire workload can be computed. The cost value can reflect a cost of constructing and collecting statistics on the associated statistical view. A set of the candidate views most beneficial for handling the workload can be determined based upon the computed benefit values and computed cost values. A generalization phase that augments the candidate view set with higher value candidate views for consideration during the recommendation phase. The optimum subset of views from the determined set of candidate views can be recommended, which can cause them to be constructed and utilized by a database optimizer.