Counting Bloom Filters for Database Histogram Statistics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In Enterprise Data Warehouse environments, updating histogram statistics for relational databases is time-consuming, leading to scalability issues and stale statistics, which affect the quality of query plans due to frequent data changes and the inefficiency of existing methods like 'direct sample' and 'sample table' approaches.
Innovation Solution
Employing Counting Bloom Filters (CBFs) to identify unique values and their frequencies in table columns, allowing for the construction of equal-height histograms in a memory-efficient and fast manner, using a series of CBF instances and optimized sorting to reduce memory usage and processing time.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional methods (direct sample or sample table) are used to update histogram statistics, then accurate statistics can be obtained, but the updating process is time-consuming and consumes excessive memory
Solution Approach 1:
The patent introduces Counting Bloom Filters (CBFs) as an intermediary data structure between the sample data and the histogram construction process. CBFs efficiently track unique values and their frequencies during the ETL phase, serving as a mediator that enables fast statistics updates without requiring traditional memory-intensive sample tables or full direct sampling approaches
Solution Approach 2:
The patent changes the parameter of memory usage by employing CBFs with configurable bit arrays and hash functions, allowing the system to achieve the same statistical accuracy with significantly reduced memory allocation compared to traditional methods. The CBF parameters (bit array size, number of hash functions) are optimized to balance between memory efficiency and statistics accuracy
2Measurement precision
If traditional methods (direct sample or sample table) are used to update histogram statistics, then accurate statistics can be obtained, but the process consumes excessive memory resources
Solution Approach 1:
The patent introduces Counting Bloom Filters (CBFs) as an intermediary data structure between the sample data and the histogram construction process. CBFs efficiently track unique values and their frequencies during the ETL phase, serving as a mediator that enables fast statistics updates without requiring traditional memory-intensive sample tables or full direct sampling approaches
Solution Approach 2:
The patent employs CBFs as temporary, disposable data structures that are created during the ETL phase to collect statistics, then discarded after histogram construction. These CBF instances consume minimal memory compared to persistent sample tables, and their temporary nature allows aggressive memory management without impacting long-term system performance
3Reliability
If frequent updates are performed to keep statistics current with changing data, then query plan quality is maintained, but the system experiences scalability issues due to time-consuming updates
Solution Approach 1:
The patent enables continuous statistics updates during the ETL phase by using CBFs that can be incrementally updated as data flows through the system. Instead of batch processing entire datasets, the CBF structure allows continuous tracking of unique values and frequencies, maintaining statistical accuracy while the system operates, thus supporting frequent updates without scalability penalties
Solution Approach 2:
The patent replaces the mechanical, time-consuming process of traditional histogram construction (sorting, sampling, and manual counting) with a more efficient mechanism based on CBFs and hashing. This substitution eliminates the need for expensive sorting operations and enables faster, more scalable statistics updates that can keep pace with frequent data changes in Enterprise Data Warehouse environments
Data Source
AI summary
A method of managing a database including creating an initial counting bloom filter (CBF) instance having an array of counters and hash functions that map an inserted value to the array of counters, and designating the initial CBF instance as a current CBF instance, and sequentially inserting each value of a sample data set of a table column into the hash functions of the current CBF instance and incrementing counters of the array of counters to which the value is mapped. The method further includes, prior to inserting each value into the hash functions of the current CBF instance, when a number of counters of the array of counters having non-zero values is at least at a threshold level, designating the current CBF instance as an old CBF instance, creating a new CBF instance having an array of counters and hash functions that map an inserted value to the array counters, and designating the new CBF instance as the current CBF instance.