Database Statistics Segmentation for Query Execution Accuracy
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems face inaccuracies in query execution plans due to the generation of statistics based solely on committed data, even when queries return both committed and uncommitted data, leading to resource wastage and delayed throughput.
Innovation Solution
A method and system that determine and generate separate statistics for committed and uncommitted database values, allowing for accurate query execution plans by distinguishing between different isolation levels and transaction states.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If statistics are generated based solely on committed data, then data consistency is maintained, but query execution plan accuracy deteriorates when queries access uncommitted data
Solution Approach 1:
The patent segments statistics generation into two distinct paths: one for committed data and another for uncommitted data. The statistics manager generates separate statistics sets based on the isolation level, allowing queries to access appropriate statistics without compromising data consistency or plan accuracy
Solution Approach 2:
The patent implements dynamic statistics generation that adapts to different isolation levels. The statistics manager dynamically determines whether to generate statistics for committed data only, uncommitted data only, or both, based on the query's isolation level requirements
2Measurement precision
If separate statistics are generated for committed and uncommitted data, then query execution plan accuracy improves, but system complexity increases
Solution Approach 1:
The statistics manager is designed as a universal component that handles multiple isolation levels through a unified interface. It can generate statistics for committed data, uncommitted data, or both, based on the query requirements, without requiring separate management systems for each case
Solution Approach 2:
The patent introduces the statistics manager as an intermediary component between the query optimizer and the database data. This mediator handles the complexity of statistics generation for different isolation levels, shielding the rest of the system from complexity while providing accurate statistics
3Reliability
If statistics are generated only for committed transactions, then transaction safety is maintained, but resource efficiency deteriorates due to inaccurate execution plans
Solution Approach 1:
The patent applies local quality by generating statistics with different levels of detail and scope based on the specific query's isolation level requirements. For queries that can access uncommitted data, more comprehensive statistics are generated locally for those data sets, improving resource efficiency without compromising transaction safety for queries that require it
Data Source
AI summary
A computer-implemented method for determining a query execution plan based on transaction state may include determining that a first set of database values in a database table are in a committed transaction state and that a second set of database values in the database table are in an uncommitted transaction state. In response to the determining, the first set of database values may have a first set of statistics generated and the second set of database values may have a second set of statistics generated. The first and second set of statistics may be for use in determining a query execution plan.