Database Table Statistics Update Using Mini-Bins

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Relational database management systems face challenges in maintaining up-to-date table statistics, especially for large tables, as collecting data distributions is costly and time-consuming, leading to suboptimal query plan selections due to stale data when data manipulation patterns are non-random.

Innovation Solution

Implementing a method for continuous automatic updating of table statistics using mini-bins, where mini-bins represent data distribution for a portion of rows, allowing for dynamic creation and consolidation into distribution bins, leveraging change data capture techniques to track and update statistics in real-time, especially for non-random data manipulation patterns.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Measurement precision

If histogram gathering is performed frequently to maintain up-to-date statistics, then query plan selection accuracy is improved, but system performance and resource utilization deteriorate due to costly full table scans and sorting operations

Engineering Contradiction:

Improvestatistics accuracyVSAvoidsystem performance

Core Design Contradiction:

Measurement precisionVSProductivity

Solution Approach 1:

The patent segments the table into multiple partitions, each with its own mini-bin statistics structure. Instead of performing a full table scan to update statistics for the entire table, the system updates only the relevant partitions. This segmentation allows statistics to be maintained more frequently and efficiently, improving statistics accuracy without proportionally increasing the overall system burden.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent performs preliminary actions by maintaining mini-bin statistics structures that are continuously updated in the background as data is inserted, updated, or deleted. These mini-bins pre-calculate and store distribution information in a condensed format, so when a full histogram is needed, it can be quickly assembled from the pre-computed mini-bins rather than performing a costly full table scan and sort operation at query time.

Inventive Principle:

Principle #10Preliminary action

2Productivity

If histogram gathering is performed less frequently to reduce system burden, then system performance is improved, but query plan selection accuracy deteriorates due to stale statistics

Engineering Contradiction:

Improvesystem performanceVSAvoidstatistics accuracy

Core Design Contradiction:

ProductivityVSMeasurement precision

Solution Approach 1:

The patent implements continuous updating of mini-bin statistics structures in the background as data manipulation operations occur. This continuous action ensures that statistics remain current without requiring frequent interruptive full histogram gathers. The mini-bins are continuously maintained with distribution information, allowing the system to achieve both good performance (by avoiding frequent full scans) and accurate statistics (by having continuously updated information available).

Inventive Principle:

Principle #20Continuity of useful action

3Measurement precision

If full table scans are performed to collect accurate data distributions, then data distribution accuracy is improved, but time consumption and resource usage worsen

Engineering Contradiction:

Improvedata distribution accuracyVSAvoidtime consumption

Core Design Contradiction:

Measurement precisionVSLoss of time

Solution Approach 1:

The patent creates mini-bin copies of the distribution information for each partition. Instead of performing full table scans to collect data distributions, the system maintains condensed copies of distribution statistics in the mini-bin structures. These mini-bins capture the essential distribution characteristics in a compact format that can be quickly assembled into full histograms when needed, dramatically reducing the time and resources required while maintaining distribution accuracy.

Inventive Principle:

Principle #26Copying

4Loss of time

If mini-bins are maintained for continuous statistics tracking, then statistics freshness is improved, but memory usage and storage requirements worsen

Engineering Contradiction:

Improvestatistics freshnessVSAvoidstorage requirements

Core Design Contradiction:

Loss of timeVSQuantity of substance

Solution Approach 1:

The patent applies local quality by maintaining mini-bin structures at the partition level rather than requiring full table-level statistics structures. Each partition has its own condensed mini-bin that tracks only the distribution information relevant to that partition. This localized approach reduces overall storage requirements compared to maintaining full histograms for the entire table, while still providing fresh, partition-specific statistics for efficient query optimization.

Inventive Principle:

Principle #3Local quality

Data Source

PatentUS10210238B2Continuous automatic update statistics evaluation using change data capture techniques

Publication Date: 2019.02.19 INTERNATIONAL BUSINESS MACHINE CORPORATION

AI summary

In a method for table statistics update, in response to a request to modify a row in a target table, a database management system inspects a log record for the modified row in the target table, determines that the target table is configured for continuous statistics evaluation, and determines whether a mini-bin related to the modified row exists. When the mini-bin related to the modified row does not exist, the database management system creates the mini-bin. When the mini-bin related to the modified row exists, the database management system aggregates the modified row into the mini-bin, and when the mini-bin is determined to have reached a threshold size, stores the mini-bin to a disk. The database management system then consolidates a plurality of mini-bins stored in the disk and corresponding to the target table into a set of bins.