Index Join Operator Range Predicate Handling
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database query optimization techniques struggle to efficiently handle join operations with ranges in predicates, as index join optimizations fail when range predicates are used, leading to erroneous results.
Innovation Solution
The implementation of a system that includes a data processor and memory instructions to generate and execute a query plan with an index join operator. This operator uses a join handler to retrieve value identifiers from a sorted dictionary, allowing for efficient execution of index joins over ranges by executing the join multiple times for each value identifier in the range.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If index join optimization is applied to join operations, then query execution speed is improved, but the technique fails when range predicates are used, leading to erroneous results
Solution Approach 1:
The patent segments the range predicate into multiple discrete value identifiers by querying the sorted dictionary. Instead of treating the range as a continuous interval that causes optimization failures, it divides the range into individual value points (first value identifier, second value identifier, and intervening value identifiers) that can be processed separately by the index join operator, thereby maintaining both speed and accuracy.
Solution Approach 2:
The patent introduces a sorted dictionary as an intermediary data structure between the range predicate and the index join operation. The join handler uses this dictionary to translate abstract range boundaries into concrete value identifiers, serving as a mediator that enables the index join to correctly handle range predicates without losing accuracy or performance.
2Reliability
If traditional join operations are used without index optimization, then correctness is maintained, but query performance deteriorates
Solution Approach 1:
The patent changes the parameter representation from continuous range values to discrete value identifiers obtained from the sorted dictionary. By transforming the range predicate parameters into a format suitable for index join operations (specific value identifiers rather than continuous ranges), it enables both correct results and high performance through index optimization.
3Reliability
If index join operator executes join multiple times for each value identifier, then complete result set is obtained, but execution complexity increases
Solution Approach 1:
The patent segments the join operation into multiple executions, each handling a specific value identifier from the range. While this requires multiple executions, the segmentation is systematic and managed by the join handler, which orchestrates the sequence of executions for the first value identifier, second value identifier, and intervening value identifiers, making the complexity manageable and predictable.
Data Source
AI summary
In some implementations, there is provided a method including receiving a query request including a join, wherein the join includes a range between a first predicate of the join and a second predicate of the join; generating a query plan including an index join operator; executing the query plan including the index join operator including getting, from the sorted dictionary, the first value identifier, the second value identifier, and the one or more intervening value identifiers between the first value identifier and the second value identifier and executing the index join operator using the first value identifier, the second value identifier, and the one or more intervening value identifiers to obtain a result set.