Eliminating Unnecessary Database Statistics for Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems lack an automated method to determine and remove unnecessary statistics collections, leading to resource-intensive data scanning and sorting, especially for large tables, as users struggle to identify which columns and indexes truly benefit from statistics collection, resulting in inefficient query optimization.
Innovation Solution
A method and system that automatically identifies unnecessary statistics collections by regenerating query execution plans without specific statistics and comparing them to original plans, using a Statistics Analysis function that includes a Query Analyzer, Search Engine, Statistics Simulator, and Plan Comparer to enumerate candidate sets of statistics for removal, ensuring minimal necessary statistics are retained for maintaining workload performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If statistics are collected on all columns and indexes to improve query optimization, then query performance is improved, but system resources are consumed excessively during statistics collection
Solution Approach 1:
The patent extracts and removes unnecessary statistics collections by automatically identifying which statistics are truly needed for query optimization. The system analyzes query workloads and eliminates redundant statistics, keeping only the minimal necessary set to maintain optimization accuracy while significantly reducing resource consumption during statistics collection.
Solution Approach 2:
The patent changes the parameter of statistics collection from comprehensive (all columns and indexes) to selective (only necessary statistics). By dynamically determining which statistics are needed based on actual query patterns and optimizer plan selection, the system optimizes the balance between optimization accuracy and resource consumption.
2Use of energy by moving object
If users manually identify and collect statistics on specific columns and indexes, then resource consumption is reduced, but users require detailed knowledge of query optimizer processes which is difficult to acquire
Solution Approach 1:
The patent implements self-service by enabling the database system to automatically analyze its own query workloads and determine which statistics are necessary. The system performs self-diagnosis through workload analysis and automatically generates recommendations for statistics collection, eliminating the need for users to manually identify required statistics while maintaining resource efficiency.
Solution Approach 2:
The patent introduces feedback mechanisms where the system continuously monitors query execution and optimizer plan selection, using this information to refine its statistics recommendations. The feedback loop allows the system to learn from actual usage patterns and improve its automatic statistics identification over time.
3Adaptability or versatility
If users accumulate statistics collections over time to handle workload changes, then query optimization adapts to workload, but the number of statistics collections grows to hundreds or thousands making manual management impossible
Solution Approach 1:
The patent extracts and removes redundant statistics from the accumulated collections. By analyzing the current workload and comparing it against existing statistics, the system identifies and eliminates unnecessary statistics, maintaining only the minimal necessary set to adapt to workload changes while preventing uncontrolled growth in statistics management complexity.
Solution Approach 2:
The patent makes statistics collection management dynamic by continuously analyzing workload changes and adjusting the statistics collections accordingly. The system dynamically adds necessary statistics when workload patterns change and removes redundant statistics, creating a living, self-adjusting statistics management system rather than a static accumulated collection.
Data Source
AI summary
A method and system for eliminating unnecessary statistics collections for query optimization in a database stored on a computer. Statistics are unnecessary when a re-generated query execution plan that does not use the statistics is equivalent to an original query execution plan that uses the statistics. To determine this, an original query execution plan is created for each query in a specified workload using the statistics in the database. A search is performed of the statistics in order to enumerate one or more candidate sets of statistics to be eliminated. One or more of the candidate sets of statistics are removed from consideration prior to creating the re-generated query execution plan for each query in the specified workload. The re-generated query execution plan, which was created with one or more of the candidate sets of statistics removed from consideration, is compared with the original query execution plan, which was created using the statistics, in order to determine whether the re-generated query execution plan and the original query execution plan are equivalent. The one or more of the candidate sets of statistics removed from consideration are identified as unnecessary when the re-generated query execution plan and the original query execution plan are equivalent.