Incremental Statistics Tree Maintenance for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for updating statistics in database query optimizers are inefficient, leading to inaccurate estimates due to outdated histograms, especially in databases with a large number of relations, and require significant system resources.
Innovation Solution
A database management system that uses a query optimizer to organize statistics into a statistics tree structure with a root node for global statistics, internal nodes for summary statistics, and leaf nodes for disjoint data ranges, performing transformation operations to minimize system resources needed for updates, such as merging or rearranging leaf nodes based on cost functions and balancing the tree structure.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If statistics are periodically recomputed from updated data, then statistics accuracy is improved, but system resource consumption and computation overhead increase significantly
Solution Approach 1:
The patent segments the statistics maintenance process by dividing the database into multiple partitions and maintaining separate histograms for each partition. This allows incremental updates to be applied selectively to only those partitions that have changed, rather than recomputing all statistics from scratch. The segmentation enables the system to maintain accurate statistics while reducing overall computation overhead by processing only the necessary subset of data.
Solution Approach 2:
The patent applies preliminary action by pre-computing and storing histogram data for each partition before it is needed for query optimization. When data changes occur, the pre-organized partition structure allows for efficient incremental updates. The system prepares statistics in advance in a structured format that facilitates quick refreshes without requiring full recomputation, thus maintaining accuracy while conserving resources.
2Productivity
If histograms are kept up-to-date frequently, then query optimization efficiency is improved, but computation overhead and system resources increase
Solution Approach 1:
The patent segments the database into multiple partitions, each with its own histogram. This segmentation allows the query optimizer to access statistics for only the relevant partitions needed for a given query, rather than processing or updating all partitions. The selective access to partitioned statistics improves query optimization efficiency while reducing the time and computational overhead associated with maintaining and scanning all statistics.
Solution Approach 2:
The patent applies local quality by maintaining different histogram granularities and update frequencies for different partitions based on their specific characteristics and change rates. Frequently changing partitions can be updated more often, while stable partitions require less frequent updates. This localized approach to statistics maintenance optimizes query performance for each partition's specific needs while minimizing overall computation overhead.
3Measurement precision
If full statistics recomputation is performed, then statistics accuracy is improved, but the effort and time required increase significantly
Solution Approach 1:
The patent segments the statistics maintenance process into partition-level incremental updates rather than full database-wide recomputation. Each partition's histogram can be updated independently and incrementally as data changes occur within that partition. This segmentation dramatically reduces the time required to maintain accurate statistics, as only the changed partitions need updating rather than the entire database.
Solution Approach 2:
The patent implements continuity of useful action by maintaining histograms in an incremental update mode that continuously reflects data changes without requiring periodic full recomputation. The partitioned structure allows the system to continuously maintain accurate statistics through small, incremental updates as data changes occur, rather than interrupting operations for lengthy full recomputation cycles. This continuous incremental maintenance preserves accuracy while minimizing update time.
Data Source
Figure 1
Figure 2
Figure 3A
AI summary
A database management system that manages data in a database includes a query optimizer that performs query optimization based on statistics related to data in the database. The query optimizer incrementally updates the statistics. The query optimizer organizes the statistics related to the data in the database into a statistics tree structure having a root node corresponding to global statistics, internal nodes corresponding to summary statistics of descendant nodes, and leaf nodes corresponding to disjoint data ranges in the database. The query optimizer performs statistics tree transformation operations on the statistics tree structure. The transformation operations transform the statistics tree structure into a form that at least partially minimizes the system resources needed to update the statistics. The query optimizer updates the statistics corresponding to those nodes of the statistics tree structure where a change has occurred in the disjoint data ranges.