Multi-Index Join Planning for Fewer Rows Read
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query processing methods are inefficient in handling large databases due to the lack of effective utilization of index scans, leading to increased processing time and resource consumption, particularly in join operations involving disjunction and conjunction conditions.
Innovation Solution
The implementation of multi-index augmented nested loop join methods that utilize multiple indexes to generate query plans, optimizing join operations by employing index scans and set operations like Tuple Identifier Union and Intersection, thereby reducing the number of rows read during query execution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional join operations are used without multi-index optimization, then the query processing is simpler to implement, but the processing time and resource consumption increase significantly
Solution Approach 1:
The patent segments the join operation by identifying disjunction and conjunction conditions separately, creating distinct query paths for each type. This segmentation allows the system to apply optimized index scan strategies specifically tailored to each condition type, improving overall query processing efficiency without overwhelming complexity
Solution Approach 2:
The patent performs preliminary analysis of join conditions to identify disjunction and conjunction patterns before executing the actual join operation. By pre-processing and categorizing conditions, the system can prepare optimized execution plans in advance, reducing processing time during query execution
2Loss of time
If index scans are not utilized effectively, then the query execution is simpler, but the number of rows read and processing time increase
Solution Approach 1:
The patent applies different index scan strategies locally based on the specific type of condition (disjunction or conjunction) being processed. By tailoring the index utilization approach to each condition type, the system achieves optimal query execution time without requiring a completely complex overall architecture
Solution Approach 2:
The patent changes the execution parameters dynamically based on the identified condition type. For disjunction conditions, it applies one set of index scan parameters, while for conjunction conditions, it applies different optimized parameters, allowing efficient query execution adapted to each specific scenario
3Quantity of substance
If multiple indexes are used for join operations, then the number of rows read is reduced, but the computational overhead for determining the query plan increases
Solution Approach 1:
The patent changes the query optimization parameters based on the specific pattern of conditions identified. By recognizing disjunction and conjunction patterns, the system selects appropriate index combinations and scan strategies, reducing the number of rows read while managing optimization complexity through pattern-based decision making
Data Source
AI summary
A system and method for efficient query processing including determining a first intermediate result set by applying a set operation to a first result set and a second result set, the join query including a join operation on a first table and a second table and including a first condition and a second condition, the first condition based on a first index of the second table, the second condition based on a second index of the second table, the first result set obtained using a first index scan of the second table, the second result set obtained using a second index scan of the second table; determining a second intermediate result set by performing a scan of the first table; determining a final result set by applying a join to the second intermediate result set and at least a portion of the first intermediate result set.