Automatic Statistical View Generation for Database Optimizer Accuracy
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face challenges in accurately estimating the cardinality of join operations, leading to optimizer errors, and manually declaring and maintaining statistical views is impractical due to high costs and overhead.
Innovation Solution
A method to automatically generate statistical views based on collected optimization and execution data, focusing on sub-expressions with errors or difficulties, and reducing redundancy by eliminating duplicates and generalizing similar views, while balancing costs with error minimization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If statistical views are manually declared to improve optimizer performance, then optimizer accuracy improves, but maintenance cost and storage overhead increase significantly
Solution Approach 1:
The system automatically generates and maintains statistical views without manual intervention. The database system self-manages the creation, update, and deletion of statistical views based on workload analysis, eliminating the need for database administrators to manually declare and maintain these views while still achieving accurate optimization
Solution Approach 2:
The system dynamically adjusts the number and type of statistical views based on workload characteristics and performance requirements. By changing parameters such as workload complexity, query patterns, and performance thresholds, the system optimizes the balance between optimizer accuracy and maintenance overhead
2Reliability
If all possible statistical views are generated to minimize optimizer errors, then optimization accuracy improves, but storage requirements and processing overhead become unmanageable
Solution Approach 1:
The system extracts and generates only the necessary statistical views required for accurate optimization, rather than generating all possible statistical views. By identifying and focusing on critical sub-expressions that impact optimization accuracy, the system reduces storage overhead while maintaining reliability
Solution Approach 2:
The system generates statistical views selectively based on workload requirements rather than comprehensively for all possible operations. This partial action approach ensures sufficient optimization accuracy for actual workload needs without the excessive storage and processing overhead of generating all possible statistical views
3Reliability
If statistical views are maintained for all operations to ensure accurate cost estimation, then optimizer reliability improves, but the time to search and compile execution plans increases
Solution Approach 1:
The system extracts and maintains only the statistical views necessary for accurate cost estimation of actual workload operations. By eliminating unnecessary statistical views, the system reduces the time required for the optimizer to search and evaluate plans while maintaining reliability for critical operations
Data Source
AI summary
A method, computer program product, and system for managing statistical views in a database system are provided. The method, computer program product, and system provide for collecting data relating to optimization and execution of a workload in the database system and automatically generating a set of one or more statistical views based on the collected optimization and execution data.