Distributed Database Outer Join via Node Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The execution efficiency of outer join operations in distributed databases is hindered by data redistribution and the generation of redundant results, leading to inefficiencies and inaccuracies, particularly when dealing with large datasets and unmatched rows.
Innovation Solution
A method and system that broadcast the first dataset to a subset of database nodes for outer join operations and another subset for inner join operations, aggregating results to minimize data transmission and compute redundant data, ensuring accurate execution by removing NULL values through a GROUP BY operation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If outer join operation is performed on all database nodes, then complete outer join result is obtained, but large amount of data redistribution occurs reducing execution efficiency
Solution Approach 1:
The patent divides the distributed database nodes into two subsets: a first subset for performing outer join operations and a second subset for performing inner join operations. This segmentation allows different join strategies to be applied to different node groups, optimizing both accuracy and efficiency. The first subset ensures complete outer join results while the second subset handles matching rows more efficiently without full data redistribution.
Solution Approach 2:
The patent applies different join operation qualities to different subsets of database nodes. The first subset receives the complete outer table data for accurate outer join execution, while the second subset receives only the necessary inner table data for efficient inner join execution. This local differentiation optimizes resource usage and execution efficiency for each subset's specific requirements.
2Reliability
If outer join operation is performed on distributed database, then unmatched rows are returned, but redundant results are generated requiring additional processing
Solution Approach 1:
The patent segments the result aggregation process into two distinct paths: outer join results from the first subset and inner join results from the second subset. By separating these result streams before aggregation, the system maintains semantic integrity while reducing redundancy. The aggregation node receives structured results from both subsets, making the combination process more efficient and less complex.
3Ease of operation
If first data set is broadcast to all database nodes, then outer join can be executed on each node, but data transmission volume increases
Solution Approach 1:
The patent segments the target database nodes into two groups: the first subset that receives the complete first data set for outer join operations, and the second subset that receives only necessary data for inner join operations. This segmentation reduces overall data transmission volume while maintaining the ease of execution by ensuring each subset receives appropriate data for its designated operation type.
Solution Approach 2:
Different data transmission qualities are applied to different node subsets. The first subset receives complete first table data locally for accurate outer join execution, while the second subset receives minimized data locally for efficient inner join execution. This local optimization reduces total network transmission costs while maintaining operational simplicity for each subset.
Data Source
AI summary
The specification provides a system, method, and storage medium for executing an outer join command on a first table and a second table in a distributed database. The method may comprise: broadcasting the first data set to a plurality of database nodes storing the second data set; dividing the plurality of database nodes into a first subset and a second subset; obtaining, an OUTER JOIN result based on the first data set broadcasted to each database node of the first subset and the portion of the second data set stored therein; obtaining, an INNER JOIN result based on the first data set broadcasted to each database node of the second subset and the portion of the second data set stored therein; and returning a result to the OUTER JOIN command by aggregating the OUTER JOIN result and the INNER JOIN result.