Bitmap-Based Count Distinct Query Rewrite in Relational SQL
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional methods for implementing a COUNT DISTINCT function in a multi-node database system, such as sorting or hashing, require significant processing resources and do not scale efficiently with large datasets, leading to overhead and performance issues.
Innovation Solution
The use of materialized views with bitmap data structures to represent distinct values of target expressions, allowing for efficient counting of distinct values by rewriting queries to utilize stored bitmap data, reducing the need for extensive sorting or hashing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If sorting is used to implement COUNT DISTINCT, then distinct values can be identified and removed, but processing resources and time increase dramatically with large datasets
Solution Approach 1:
The patent pre-computes and stores bitmap data structures that represent distinct values in materialized views before queries are executed. This preliminary action allows the system to avoid expensive sorting operations during query execution, as the bitmap data is already prepared and can be directly queried to obtain distinct counts.
Solution Approach 2:
The patent creates a bitmap representation (a simplified copy) of the distinct values in the data. Instead of working with the actual tuples and sorting them, the system uses the bitmap as a compact copy that preserves the essential information about which values are distinct, enabling fast counting without re-processing the original data.
2Productivity
If hashing is used to implement COUNT DISTINCT, then processing is less computationally intensive than sorting, but still consumes large amounts of processing resources
Solution Approach 1:
The patent uses bitmap data structures as a compact representation (copy) of the distinct values, replacing the need for hashing operations. The bitmap directly encodes the presence of distinct values, allowing the system to count distinct values by simply counting set bits in the bitmap, which is much more efficient than hashing and requires significantly fewer processing resources.
Solution Approach 2:
The patent changes the data representation from raw tuple values to bitmap encodings. This parameter change transforms the problem from one requiring hashing operations to one that can be solved by simple bit counting operations, dramatically reducing the processing resources needed while maintaining the ability to accurately count distinct values.
3Measurement precision
If sorting and removing duplicates is used, then distinct count can be obtained, but processing time and resources increase with dataset size
Solution Approach 1:
The system performs the expensive distinct value identification action in advance by pre-computing bitmap data structures and storing them in materialized views. This preliminary computation eliminates the need to re-sort and re-process data for each query, as the distinct value information is already captured in the bitmap and can be retrieved instantly.
Solution Approach 2:
The patent creates a compressed copy of the distinct value information using bitmap data structures. This copy contains all the necessary information to determine distinct counts without requiring re-processing of the original large datasets, enabling fast query execution while maintaining accurate distinct count results.
Data Source
AI summary
Techniques are described for storing and maintaining, in a materialized view, bitmap data that represents a bitmap of each possible distinct value of an expression and rewriting a query for a count of distinct values of the expression using the materialized view. The materialized view contains bitmap data that represents a bitmap of each possible distinct value of a first expression, and aggregate values of additional expressions, and is stored in memory or on disk by a database system. The database system receives a query that requests a number of distinct values, of the first expression, and an aggregate value for an additional expression. In response, the database system, rewrites the query to: compute the number of distinct values by counting the bits in the bitmap data of the materialized view that are set to the first value, and obtains the aggregate value for the additional expression in the materialized view.