Column-Range Indexes for Faster Multistage Database Joins
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems face performance deterioration in join processing due to the need to search the entire range of the inner table for each record, especially when search conditions or partition keys are absent, leading to inefficient processing.
Innovation Solution
A database management apparatus that uses column range indexes to limit the scanning range of inner tables during join processing, creating filters to scan only specific ranges based on the join columns of current and next stages, irrespective of search conditions or partition keys, thereby improving performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Area of stationary object
If the scanning range of the inner table is limited using existing methods (record sets, search conditions, partition keys, or bloom filters), then the scanning range can be restricted to some extent, but the processing performance deteriorates because the range needs to be searched for each record
Solution Approach 1:
The patent pre-calculates and stores the scanning range of the inner table before the join operation using column range indexes. This preliminary action eliminates the need to compute the scanning range for each record during the join, thereby restricting the scanning area while maintaining high processing performance. The filter is created once based on the outer table's value range and applied to the entire inner table scan.
2Adaptability or versatility
If search conditions or partition keys are not assigned to the join, then the join can be performed without additional constraints, but the scanning range of the inner table cannot be limited
Solution Approach 1:
The patent introduces column range indexes as an intermediary data structure that enables scanning range limitation without requiring search conditions or partition keys. The column range index acts as a mediator between the outer table's value range and the inner table's physical storage structure, allowing the system to leverage the natural ordering of data to restrict the scanning area while maintaining join flexibility.
3Reliability
If the entire inner table is scanned for each join operation, then all possible matches are guaranteed to be found, but the processing time increases significantly
Solution Approach 1:
The patent applies local quality by making the scanning range property specific to each join operation based on the outer table's value range. Instead of uniformly scanning the entire inner table, the system determines a localized scanning range using column range indexes that corresponds to the specific values present in the outer table. This ensures that only the relevant portion of the inner table is scanned, maintaining result completeness while reducing processing time.
Data Source
AI summary
The scanning range of an inner table can be limited in the join processing irrespective of the existence of search conditions or a partition key, and the performance of the join processing can be improved. There is a column range index representing a range of values stored in a table. When a join result of a current stage is an outer table related to a join of a next stage in a join of each stage of the join processing, a database management apparatus identifies whether there is a possibility that an inner table related to a join of a next stage includes a hit record in an inner table related to a join of a current stage based on a column range index corresponding to a join column of a current stage and a column range index corresponding to a join column of a next stage.