Database Join Range Filtering Using Column Range Indexes
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 are absent or the join is not based on a partition key.
Innovation Solution
A database management apparatus that uses column range indexes to limit the scanning range of the inner table by creating filters based on the join columns of current and next stages, allowing the system to scan only the identified ranges during join processing.
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 set, search conditions, partition key, or bloom filter), then the scanning range can be restricted under specific conditions, but the processing performance deteriorates when searching for each record or when search conditions/partition keys are absent
Solution Approach 1:
The patent pre-calculates and stores column range indexes for join columns before the join operation. These indexes represent the minimum and maximum values for each column in different data ranges. During the join operation, the system uses these pre-computed indexes to quickly determine the scanning range without needing to search the entire inner table or perform per-record calculations, thus improving processing performance while limiting the scanning range.
Solution Approach 2:
The patent changes the approach from using traditional filtering mechanisms (search conditions, partition keys, bloom filters) to using column range indexes that store minimum and maximum value parameters. This parameter-based approach allows the system to efficiently determine scanning ranges by comparing index values rather than evaluating complex filter conditions for each record, resolving the contradiction between range limitation and processing performance.
2Area of stationary object
If the scanning range is limited using search conditions or partition keys, then the inner table scanning can be restricted, but the scanning range cannot be limited when search conditions are not assigned or the join is not based on a partition key
Solution Approach 1:
The patent creates column range indexes for join columns that can be universally applied to any join operation regardless of whether search conditions or partition keys are present. The indexes serve multiple purposes: they enable range limitation in joins with search conditions, provide scanning range information for partitioned joins, and crucially, allow range limitation in joins without search conditions or partition keys. This universal mechanism resolves the contradiction by making the scanning range limitation capability applicable to all join scenarios.
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.