Database Query Optimization via Key Value Density Analysis
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database query optimization methods require significant resources to build and use bit maps, which can be inefficient when dealing with queries that only need a subset of the answer set, leading to performance degradation as database volume and user access increase.
Innovation Solution
Determining the density of key values over specific ranges of rows and using this information to optimize database queries by building bit maps only over denser regions, reducing resource usage and improving query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If bit maps are built over the entire table to improve query performance, then query execution speed is improved, but resource consumption increases significantly
Solution Approach 1:
The patent divides the database table into multiple ranges based on row ranges, and builds bit maps only for specific ranges rather than the entire table. This segmentation allows the system to focus computational resources on relevant portions of the data, reducing overall resource consumption while maintaining query performance for targeted queries.
Solution Approach 2:
The patent applies different bit map density strategies to different ranges of the table based on local characteristics. By determining key value density for each range and selectively building bit maps only for ranges where it provides benefit, the system optimizes resource usage locally rather than applying a uniform approach across the entire table.
2Speed
If bit maps are built for all key values to improve query performance, then query execution speed is improved, but resource usage increases
Solution Approach 1:
The patent implements partial action by building bit maps only for key values and ranges where the density analysis indicates it will be beneficial. Rather than exhaustively building bit maps for all possible key values across the entire table, the system performs selective bit map construction based on density thresholds and query patterns, reducing resource usage while maintaining effectiveness for the most relevant queries.
3Quantity of substance
If the database volume increases to store more information, then data capacity is improved, but query performance degrades
Solution Approach 1:
The patent segments the growing database into manageable row ranges and applies density-based bit map construction to each segment independently. This allows the system to handle increased data capacity by processing and optimizing smaller chunks at a time, preventing the performance degradation that would result from attempting to optimize the entire large table at once.
Data Source
AI summary
A method, apparatus and program product that determine a density of a key value referenced in a database query over at least one range of rows, and utilize the determined density to optimize the database query. By doing so, the density of various key values may be determined and compared, and a bit map may be generated over the range of rows of the key value that is denser, resulting in a reduction of resources to build and use the bit map. Moreover, from the bit map over the range of rows to be selected after using the determined density, dense regions may be identified that can be used to optimize block I/O's according to the number of rows to be selected based on the query.