Bitmap Subtraction for Range Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database query optimization methods are inefficient for processing queries with range conditions, particularly requiring numerous traversals through tree structures for large value ranges, leading to computational expense.
Innovation Solution
A method involving bitmap subtraction is introduced to optimize range-based queries, where bitmaps for the first and second ends of the range are traversed and subtracted to obtain a result bitmap, enabling efficient retrieval of rows that meet the range condition.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional tree traversal method is used to process range-based queries, then query accuracy is maintained, but computational overhead increases significantly and query performance deteriorates
Solution Approach 1:
The patent segments the range-based query processing into two distinct bitmap operations: retrieving a first bitmap representing rows with values less than or equal to the upper bound, and retrieving a second bitmap representing rows with values less than or equal to the lower bound minus one. This segmentation allows the complex range query to be decomposed into manageable bitmap operations that can be efficiently processed and combined through bitwise operations to produce the final result set.
Solution Approach 2:
The patent replaces the traditional mechanical tree traversal mechanism with a bitmap-based operational system. Instead of physically traversing the tree structure to identify rows within a value range, the system uses bitmap retrieval and bitwise operations (specifically, subtracting the second bitmap from the first bitmap) to compute the result. This substitution eliminates the computational overhead of tree traversal while maintaining query accuracy.
2Quantity of substance
If tree traversal is performed for each individual value in the range, then complete result set is obtained, but the number of operations increases and processing time increases
Solution Approach 1:
The patent applies preliminary action by pre-computing and storing bitmap representations for different value thresholds during database operations. When a range-based query is executed, the system can directly retrieve these pre-computed bitmaps corresponding to the upper and lower bounds without performing computations at query time. This preliminary preparation enables the system to quickly obtain complete result sets through simple bitmap subtraction operations, significantly reducing processing time compared to traditional methods.
Solution Approach 2:
The patent merges multiple individual value queries into a single range-based query operation. Instead of performing separate tree traversals for each value within the range and combining results incrementally, the system merges the retrieval process into two bitmap fetches (one for each bound) followed by a single bitwise subtraction operation. This merging consolidates what would be numerous sequential operations into a streamlined process that maintains result completeness while minimizing processing time.
Data Source
AI summary
A system, method, and computer program product are provided for optimizing a range-based query on a table in a database system.