Database Index Scans Using Hash Tables for Boolean Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face challenges in efficiently processing queries with multiple conditions when records are not associated with row/system identifiers, making conventional bitmap index scans infeasible.
Innovation Solution
The system processes queries using multiple indexes by performing index scans for each clause in the Boolean expression, either updating a hash table for AND operations or constructing a union of results for OR operations, and removing duplicates as necessary.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If bitmap index scans are used to process queries with multiple conditions, then query execution efficiency is improved, but the system becomes infeasible when records are not associated with row identifiers
Solution Approach 1:
The patent introduces an intermediary mechanism using hash tables to track records visited during index scans. Instead of relying on row identifiers directly, the system uses a hash table with counters as a mediator to manage and track which records have been processed, enabling the system to work with records that don't have traditional row identifiers while maintaining efficient query processing
Solution Approach 2:
The patent changes the parameter approach from using fixed row identifiers to using dynamic counter values in hash tables. By changing how records are tracked (from static row IDs to dynamic counter-based tracking), the system adapts to work with records that lack traditional identifiers while maintaining the efficiency benefits of index scanning
2Reliability
If full table scans are performed to ensure all records are checked, then complete query results are obtained, but scan time and latency increase significantly
Solution Approach 1:
The patent segments the table scan process into multiple index scan operations. Instead of scanning the entire table sequentially, the system divides the query processing into segments by executing index scans for different conditions and using hash tables to track and combine results, thereby reducing overall scan time while maintaining result completeness
Solution Approach 2:
The patent performs preliminary actions by executing index scans before full table scans. By pre-processing query conditions through index scans and tracking results in hash tables, the system can determine whether additional scanning is needed, thereby reducing unnecessary full table scans and associated latency
3Adaptability or versatility
If multiple index scans are performed to process Boolean expressions with AND and OR operations, then query processing capability is enhanced, but system complexity increases
Solution Approach 1:
The patent creates a universal hash table-based tracking mechanism that serves multiple functions: tracking records for AND operations, managing results for OR operations, and handling duplicate removal. By making the tracking mechanism multi-functional, the system enhances its Boolean expression processing capability without proportionally increasing complexity
Solution Approach 2:
The patent merges multiple tracking and processing functions into a unified hash table-based system. By combining AND operation tracking, OR operation result management, and duplicate removal into a single integrated mechanism, the system reduces the overall complexity that would otherwise arise from separate handling of each operation type
Data Source
AI summary
Techniques are disclosed relating to implementing multiple index scans. A computer system may store a database table comprising fields and indexes corresponding to those fields. The computer system may receive a request to access records based on a Boolean expression that affects a selection of records from the database table and that comprises clauses, at least two of which are joined by an AND operation. The computer system may access the requested records. The accessing may include, for a given one of the at least two clauses, performing an index scan on an index that corresponds to the given clause to identify records that satisfy that clause. The computer system may update a hash table based on the identified records and then identify the requested records based on the hash table. The Boolean expression may include clauses joined by an OR operation that are processed using multiple index scans.