Query-Specific Database Statistics Collection for Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face inefficiencies due to missing or stale statistics, which lead to suboptimal query processing times, as existing solutions either benefit only future queries or require costly and inefficient multiple optimization attempts for current queries.
Innovation Solution
A method that collects query-specific statistics just in time for optimizing current queries, generates access plans based on these statistics, and stores them for future use, thereby reducing the need for repeated optimizer calls and improving query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If multiple optimization attempts are made to benefit currently executing queries, then query optimization quality improves, but system efficiency deteriorates due to the high cost and complexity of multiple optimizer calls
Solution Approach 1:
The system collects query-specific statistics before the optimization process begins, preparing the necessary data in advance. This preliminary action eliminates the need for multiple optimization attempts, as the optimizer can directly use the pre-collected statistics to generate optimal access plans efficiently.
Solution Approach 2:
The system automatically collects and maintains query-specific statistics without requiring multiple manual optimization attempts. The statistics collection mechanism serves itself by continuously gathering relevant data, allowing the optimizer to function efficiently with accurate, up-to-date statistics already available.
2Measurement precision
If statistics are collected and updated frequently to improve current query optimization, then query processing quality improves, but resource consumption increases
Solution Approach 1:
The system collects statistics that are specifically relevant to the current query rather than collecting all possible statistics. This localized approach ensures high statistics accuracy for the specific query context while minimizing unnecessary resource consumption associated with collecting and processing irrelevant statistical data.
Solution Approach 2:
The system dynamically adjusts which statistics to collect based on the specific query characteristics and current data state. By changing the parameters of what statistics are gathered (selecting only those relevant to the current query), the system maintains high accuracy while controlling resource usage.
Data Source
AI summary
A method, system, and computer program product for managing database statistics are provided. The method, system, and computer program product provide for receiving a query for optimizing, collecting statistics specific to the query prior to generating any access plans for executing the query, and generating an access plan for executing the query based on the collected statistics.