Cost Estimation for Database Operation Trees with Incomplete Statistics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Cost-based optimizers in database systems face challenges in performing cost estimation when statistical information is incomplete, particularly in scenarios involving temporary tables, subqueries, and massive data sets, leading to limitations in generating optimal execution plans.
Innovation Solution
A method and device that determine whether acquired statistical information is complete, and if not, employ a specific cost estimation manner for operation types within an operation tree, combining estimates based on statistical and non-statistical information to generate an accumulative cost estimate, thereby enabling cost optimization even with incomplete data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If statistical information is collected for cost optimization, then cost estimation accuracy is improved, but collection cost increases and becomes a barrier to application
Solution Approach 1:
The patent applies partial action by using sampling techniques to collect statistical information from only a subset of data rather than requiring complete data collection. This allows the cost-based optimizer to perform accurate cost estimation without the high cost of collecting statistical information for entire massive data sets, thus resolving the contradiction between estimation accuracy and collection cost.
2Manufacturing precision
If complete statistical information is required for cost optimization, then execution plan quality is improved, but applicability to scenarios with temporary tables and subqueries is reduced
Solution Approach 1:
The patent applies preliminary action by pre-defining default statistical information and estimation methods for operation types that lack complete statistical data, such as temporary tables and subqueries. This allows the cost-based optimizer to proceed with cost estimation in scenarios where complete statistical information is unavailable, thereby improving both execution plan quality and adaptability.
Solution Approach 2:
The patent changes the parameter requirements by introducing flexible statistical information collection mechanisms that can adapt to different operation types. Instead of requiring complete statistical information for all operations, the system adjusts parameters to accept partial or estimated statistical data, enabling applicability to temporary tables and subqueries while maintaining execution plan quality.
3Reliability
If statistical information is collected for massive data, then cost estimation becomes possible, but the collection process becomes a barrier to application
Solution Approach 1:
The patent applies segmentation by dividing the statistical information collection process into manageable segments for different operation types. Instead of collecting statistical information for entire massive data sets at once, the system segments the data and collects statistics for specific operation types (table scans, joins, aggregates, etc.) independently, reducing the complexity of the collection process while maintaining cost estimation capability.
Solution Approach 2:
The patent introduces an intermediary mechanism in the form of a cost estimation model that bridges the gap between available statistical information and cost optimization requirements. This intermediary can work with partial or estimated statistical data rather than requiring complete data collection, thus enabling cost estimation capability without the complexity of comprehensive statistical information collection for massive data.
4Measurement precision
If traditional cost estimation models are used, then complete statistical information is required, but the model fails when statistical information is incomplete
Solution Approach 1:
The patent applies dynamics by making the cost estimation model adaptive and flexible rather than rigid. The model dynamically adjusts its behavior based on the availability of statistical information, switching between using complete statistical data for accurate estimation and using estimated or default values when data is incomplete. This dynamic approach maintains both measurement precision when data is available and robustness when data is missing.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A cost based optimizer and a cost estimation method and device. The method includes: in response to deciding that acquired statistical information is incomplete, determining a corresponding cost estimation manner according to an operation type of an operation tree relying on the statistical information (S11); determining a cost estimate of the corresponding operation type based on the cost estimation manner (S12); and determining an accumulative cost estimate of the operation tree according to the cost estimate corresponding to the operation type relying on the statistical information and a cost estimate corresponding to an operation type not relying on the statistical information (S13). Therefore, cost estimation may be performed for a temporary table and a subquery created in a running process, and is not limited by a data scale in a scenario of massive data.