Remote Database Statistics Collection for Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database query optimization is hindered by the need to balance optimization time and resource constraints, often resulting in suboptimal plans due to excessive time requirements for evaluating all possible query plans, especially with complex queries and large databases, leading to incomplete statistics collection and inefficient indexing strategies.
Innovation Solution
A method that offloads query plan generation and statistics collection to a remote system with a current copy of the dataspace, allowing for efficient resource utilization and optimization using remote processor power, enabling separate optimization of query subplans and synchronization of optimization data between local and remote caches.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Manufacturing precision
If all possible query plans are evaluated to find the optimal plan, then query optimization quality is improved, but optimization time becomes excessively long
Solution Approach 1:
The patent segments the query optimization process into distinct phases: statistics collection, query plan generation, and plan evaluation. By separating these phases and allowing them to proceed independently where possible, the system avoids the bottleneck of sequential processing while maintaining optimization quality.
Solution Approach 2:
The patent performs statistics collection as a preliminary action before query optimization. By pre-collecting and caching statistics about the database state, the system eliminates the need to repeatedly gather statistics during each optimization cycle, significantly reducing optimization time while preserving the ability to evaluate multiple query plans thoroughly.
2Manufacturing precision
If deep statistics collection is performed to improve optimization accuracy, then query plan quality is improved, but system resource consumption increases excessively
Solution Approach 1:
The patent implements partial statistics collection by selectively gathering statistics only for the portions of the database that are relevant to the current query. Instead of performing exhaustive deep statistics collection across the entire database, the system identifies and collects statistics only where needed, reducing resource consumption while maintaining optimization accuracy for the specific query at hand.
Solution Approach 2:
The patent applies local quality by collecting detailed statistics only for specific database objects and query-relevant data regions, rather than uniformly across the entire database. This targeted approach concentrates computational resources on the areas that most impact query optimization accuracy, eliminating wasted effort on irrelevant data portions.
3Speed
If indexing strategy is fully explored to improve query access efficiency, then query execution speed is improved, but optimization time increases due to DNF creation and index combination analysis
Solution Approach 1:
The patent performs index analysis and DNF (disjunctive normal form) creation as preliminary actions during the statistics collection phase, before actual query optimization begins. By pre-processing and caching index compatibility information and predicate transformations, the system eliminates the need to repeatedly perform these computationally intensive operations during query optimization, reducing indexing evaluation time while maintaining the ability to explore index strategies thoroughly.
Data Source
AI summary
A method, apparatus and program product to optimize a database query. In a first system having a first dataspace, a query plan is generated for the database query. Remote statistics collection associated with the database query is initiated on a second system having a second dataspace. The remote statistics collection uses the second dataspace, which includes a current copy of at least a portion of the first dataspace on the first system. Database statistics are collected for the first system by analyzing the second dataspace and then communicating the collected database statistics to the first system for use in optimizing database queries executed by the first system.