Outer Join Processing in Parallel Database Systems
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The performance of outer joins in parallel database management systems is hindered by significant data communication bottlenecks due to large data exchanges among processing units, leading to uneven workload distribution and reduced system efficiency.
Innovation Solution
Tables to be joined are redistributed based on join attributes before each outer join, with dangling records from the first join operation kept in their original storage modules to avoid skewing, allowing only non-dangling records to be redistributed for the subsequent join, thereby optimizing data distribution and workload balance across processing units.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If outer joins are performed in a parallel database management system with standard data distribution, then join operations can be executed across multiple processing units, but data communication bottlenecks occur due to large data exchanges among processing units
Solution Approach 1:
The patent applies preliminary action by redistributing tables based on join attributes before performing outer joins. This pre-distribution ensures that related data is co-located on the same processing units, eliminating the need for extensive data communication during the actual join operation. The redistribution is performed in advance to optimize the subsequent join execution.
Solution Approach 2:
The patent implements local quality by keeping dangling records (records without matching pairs) in their original storage modules rather than redistributing them. This localized approach reduces unnecessary data movement while maintaining join correctness, as dangling records do not require communication with other processing units to complete the join operation.
2Ease of operation
If all records are redistributed across processing units for outer join operations, then even workload distribution is achieved, but data skew increases and communication bottlenecks worsen
Solution Approach 1:
The patent applies local quality by treating dangling records differently from matched records. While matched records are redistributed to achieve even workload distribution, dangling records are kept in their original storage modules. This differentiated approach maintains operational simplicity while avoiding the complexity of redistributing all records uniformly.
Solution Approach 2:
The patent segments the data into two categories: matched records that require redistribution for balanced workload, and dangling records that can remain localized. This segmentation allows the system to apply different distribution strategies to different data segments, optimizing both workload balance and communication efficiency.
3Productivity
If dangling records are redistributed along with other records, then uniform data distribution is achieved, but a single processing unit may handle excessive data causing performance degradation
Solution Approach 1:
The patent applies local quality by keeping dangling records in their original storage modules rather than redistributing them. This localized approach prevents data skew from concentrating excessive dangling records on single processing units, thereby avoiding performance degradation while maintaining overall system efficiency.
Solution Approach 2:
The patent converts the potential harm of dangling records causing data skew into a benefit by recognizing that these records do not require redistribution. By exploiting this property, the system eliminates unnecessary communication overhead while preventing the harmful concentration of data on specific processing units.
Data Source
AI summary
To process a sequence of outer joins in a database system, the database system performs a first outer join of the sequence of outer joins. A result of the first outer join is stored in a result table stored across plural storage modules of the database system. At least a subset of records of the result table is redistributed across the storage modules according to a first join attribute of the result table, where any record of the result table that has a null value for the first join attribute is not redistributed. A second outer join of the sequence is performed using the redistributed result table and another table, where the second outer join is based on the first join attribute of the result table.