Histogram Interval Selection for Database Query Planning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face inefficiencies in query planning due to the use of average frequency in histograms, which can mislead optimal plan selection when value frequencies are significantly different, and rebuilding histograms with more intervals increases resource usage without significant reduction in deviation.
Innovation Solution
A database system that determines conditions associated with the distribution of column values and adjusts the number of histogram intervals based on these conditions, using equations to calculate a new number of intervals for rebuilding, considering reference frequency and average deviation, to improve accuracy and resource management.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If the number of histogram intervals is increased to reduce frequency deviation, then query planning accuracy is improved, but storage space and processing time increase
Solution Approach 1:
The patent implements dynamic histogram interval adjustment where the system automatically determines whether to increase or decrease the number of intervals based on monitoring query planning accuracy and frequency deviation metrics. The histogram structure is not static but adapts its granularity level over time based on actual performance feedback, resolving the contradiction by making the complexity level dynamic rather than fixed.
Solution Approach 2:
The system changes the parameter of histogram interval count based on detected conditions. When frequency deviation exceeds thresholds or query accuracy degrades, the system increases the interval count; when accuracy is sufficient, it decreases the interval count. This parameter adjustment resolves the contradiction by optimizing the balance between precision and complexity based on actual system state.
2Manufacturing precision
If the number of histogram intervals is increased to capture frequency variations, then value frequency representation accuracy is improved, but maintenance time and computational overhead increase
Solution Approach 1:
The patent implements periodic histogram rebuilding with adaptive interval adjustment. Instead of continuously maintaining high-precision histograms, the system periodically evaluates whether accuracy improvements are needed and only then increases interval count. This periodic action reduces maintenance overhead while maintaining accuracy only when necessary, resolving the time-cost contradiction.
Solution Approach 2:
The system uses simpler, coarser histograms for extended periods when accuracy requirements are met, only investing in expensive fine-grained histograms when actually needed. This approach of using 'cheap' low-interval histograms most of the time reduces maintenance time while maintaining accuracy when required.
3Speed
If average frequency is used for query planning to simplify processing, then processing speed is improved, but plan optimality deteriorates when value frequencies are significantly different
Solution Approach 1:
The patent applies local quality by creating non-uniform histogram intervals that are finer in regions where frequency variations are significant and coarser where frequencies are uniform. This allows the system to use average frequency for most intervals (maintaining speed) while providing more detailed local information where needed (improving optimality), resolving the speed-optimality contradiction.
Solution Approach 2:
The system segments the value range into intervals of varying granularity. High-frequency variation regions are segmented into more intervals, while low-variation regions use fewer intervals. This selective segmentation allows accurate frequency representation where needed without proportionally increasing processing complexity across the entire histogram, resolving the contradiction between speed and plan optimality.
Data Source
AI summary
A database system may include an array of storage devices configured to store a plurality of database tables and a plurality of histograms. Each histogram may correspond to one or more columns of a database table. The database system may further include a processor in communication with the storage device. The processor may receive an indication that a histogram is to be rebuilt, wherein the histogram has a first number of intervals. The processor may determine at least one condition associated with a distribution of column values of at least one column in the histogram. The processor may determine a second number of histogram intervals based on the at least one condition. The processor may rebuild the histogram to include the second number of intervals. A method and computer-readable medium may also be implemented.