Range Set Predicate Generation for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face inefficiencies in handling overlapping range predicates, leading to incorrect coverage and suboptimal query optimization due to the inability to effectively consolidate and process multiple range conditions.
Innovation Solution
The system converts eligible predicates to range set predicates, allowing for the consolidation of multiple range conditions into a single range set, which simplifies selectivity estimation and improves query processing by traversing histograms only once.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If multiple range predicates are processed separately, then each predicate can be evaluated independently, but the coverage algorithm cannot report correct coverage when handling overlapping range conditions
Solution Approach 1:
The patent merges multiple range predicates into a single consolidated range predicate by computing the intersection of all ranges. This consolidation allows the coverage algorithm to correctly handle overlapping range conditions by evaluating them as a unified range set, thereby resolving the coverage accuracy issue while reducing processing complexity through a single evaluation pass.
2Productivity
If the system traverses histograms multiple times for each range predicate, then each predicate can be processed independently, but resource usage increases and query optimization becomes suboptimal
Solution Approach 1:
The patent consolidates multiple range predicates into a single range predicate before histogram traversal. This merging enables the system to traverse the histogram only once for the consolidated range set, significantly reducing resource usage and improving query processing efficiency compared to multiple separate traversals.
3Reliability
If view definition access right predicates are not intersected with user-specified conditions, then query processing is simpler, but range-condition-sensitive tasks proceed with incorrect optimization
Solution Approach 1:
The patent automatically intersects view definition access right predicates with user-specified conditions to produce a consolidated range predicate. This merging ensures that range-condition-sensitive tasks receive correct optimized predicates that reflect both the view's access rights and the user's query conditions, thereby improving query optimization accuracy while managing complexity through automated consolidation.
Data Source
AI summary
A system may include a storage device configured to store a data store comprising data. The database system may further include a processor in communication with the storage device. The processor may receive a query that includes at least one predicate associated with the data. The processor may further determine that the at least one predicate is eligible to be converted to a range set predicate. The processor may further convert the at least one predicate to a range set predicate. The processor may further process the query with the range set predicate. A method and computer-readable medium may also be implemented.