Inequality Join Method Using Permutation Arrays
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing relational database join methods are inefficient, particularly for inequality joins, due to high computational and memory requirements, and existing indices like R-trees and B+-trees either lead to random I/O access or sequential disk scans, which are costly in terms of resources.
Innovation Solution
The optimized inequality join method employs permutation arrays and space-efficient bit-arrays, leveraging Bloom filter indices for fast computation, using algorithms developed on top of PostgreSQL and Spark SQL to efficiently handle sorted attribute values and reduce memory footprint.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional join methods (R-trees, B+-trees) are used for inequality joins, then join functionality is provided, but computational time and memory usage increase significantly
Solution Approach 1:
The patent segments the join computation into distinct phases: sorting the smaller table by the join attribute, creating a hash index on the sorted data, and then performing the join operation. This segmentation allows each phase to be optimized independently, improving overall computational efficiency for inequality joins
Solution Approach 2:
The patent performs preliminary sorting of the smaller table by the join attribute before executing the join operation. This preliminary action creates a sorted structure that enables more efficient join computation, particularly for inequality conditions, by reducing the search space and enabling sequential access patterns
2Ease of operation
If traditional indices (R-trees, B+-trees) are used for inequality joins, then data can be accessed, but random I/O access occurs which increases memory usage and reduces efficiency
Solution Approach 1:
The patent replaces traditional mechanical index structures (R-trees, B+-trees) with a hash-based indexing mechanism built on sorted data. This substitution eliminates the need for complex tree traversal and random I/O access, replacing it with more efficient hash-based lookups and sequential access patterns that reduce I/O operations
Solution Approach 2:
The patent changes the fundamental parameter of data organization from tree-based hierarchical structures to sorted linear structures with hash indices. This parameter change transforms the access pattern from random I/O to sequential access with hash-based direct lookup, significantly reducing I/O operations and improving data access efficiency
3Productivity
If sort-merge joins are used, then join results are obtained, but memory footprint increases due to requirements for sorted data storage
Solution Approach 1:
The patent extracts only the essential sorted structure and hash index from the smaller table, rather than requiring full sorted data storage in memory. This extraction approach allows the join operation to proceed with minimal memory footprint, using only the necessary indexing structures rather than the entire sorted dataset
Data Source
AI summary
The optimized inequality join method is a method for joining relational tables on input inequality conditions. The optimized inequality join method is a relatively fast inequality join method using permutation arrays to store positional information for sorted attributed values. Additionally, space efficient bit arrays are used to enable optimization, such as Bloom filter indices, thus providing faster computation of the join results. The method may be used, for example, for joining various inequalities associated with a variety of measured environmental conditions for raising an alarm when certain conditions are met.