Bitmap Slice Indices for Multi-Table Join Acceleration
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Bitmap indices in relational databases are ineffective in accelerating query execution for JOIN operations across multiple tables due to format incompatibility, leading to slow query execution and a poor user experience in applications relying on these databases.
Innovation Solution
The implementation of bitmap slice indices (BSIs) allows for the alignment and combination of result bitmaps from parent and child tables related via foreign key relationships, enabling direct combination of query results across multiple tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If bitmap indices are used to accelerate query execution for single-table queries, then query execution speed is improved, but bitmap indices cannot be used for JOIN operations across multiple tables due to format incompatibility
Solution Approach 1:
The patent segments the bitmap index structure into table-specific components, where each table has its own bitmap index format optimized for that table's schema. This allows bitmap indices to be constructed for individual tables while maintaining compatibility across different table structures, enabling their use in JOIN operations.
Solution Approach 2:
The patent introduces an intermediary layer that translates between different bitmap index formats from various tables. This intermediary mechanism enables the combination of results from multiple bitmap indices during JOIN operations, resolving the format incompatibility issue while preserving the speed benefits of bitmap indexing.
2Loss of time
If bitmap indices are used for single-table queries, then comparison operations are reduced and query execution is accelerated, but the same approach cannot be applied to multi-table JOIN queries
Solution Approach 1:
The approach segments the query processing into table-specific bitmap index construction phases and result combination phases. Each table's bitmap index is constructed independently to minimize comparison operations, then results are combined efficiently during JOIN operations, achieving both time reduction and JOIN capability.
Solution Approach 2:
The patent extends the bitmap index concept from a single-table dimension to a multi-table dimension by introducing a standardized bitmap format and combination mechanism. This dimensional extension allows bitmap indices to operate across multiple tables while maintaining the efficiency benefits of reduced comparison operations.
3Adaptability or versatility
If traditional query execution without bitmap indices is used, then queries can be executed across multiple tables, but query execution time increases significantly
Solution Approach 1:
The patent applies preliminary action by pre-computing and storing bitmap indices for each table before JOIN operations are executed. This pre-computation phase reduces the computational burden during actual query execution, enabling fast multi-table query processing while maintaining versatility.
Solution Approach 2:
The patent creates compact bitmap representations (copies) of table data that can be rapidly processed and combined. These bitmap copies preserve the essential information needed for JOIN operations while enabling much faster execution compared to processing the full relational data structures.
Data Source
AI summary
A database engine uses bitmap slice indices (BSIs) to combine result bitmaps derived from parent and child tables related via one or more foreign key relationships. To execute a query that involves a parent table and a child table, the database engine parses the query into a parent predicate corresponding to the parent table and a child predicate corresponding to the child table. The database engine executes the parent predicate using a parent bitmap index to generate a parent result bitmap and executes the child predicate using a child bitmap index to generate a child result bitmap. The database engine transforms the child result bitmap, based on the BSI, to generate a transformed child result bitmap having columns that correspond to the primary key of the parent table. The database engine can then combine the parent result bitmap directly with the transformed child result bitmap to generate query results.