Outer Join Optimization via Small Table Duplication
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing parallel database management systems face inefficiencies in optimizing outer joins, particularly due to the assumption that inner join optimization techniques can be applied directly to outer joins, leading to suboptimal performance in large data warehouses.
Innovation Solution
The Duplication and Efficient Redistribution (DER) technique, which involves duplicating a small table across multiple parallel units, identifying and redistributing dangling rows, forming a result by unioning inner joins and padded nulls, and saving the final result, to efficiently perform outer joins without redistributing the large table or intermediate join results.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If inner join optimization techniques are applied to outer joins in parallel DBMS, then the implementation is simpler, but the performance is suboptimal for large data warehouses
Solution Approach 1:
The patent segments the outer join operation into distinct phases: duplication of the small table across all PUs, identification of dangling rows, redistribution of dangling rows to matching PUs, and final result assembly. This segmentation allows each phase to be optimized independently, resolving the contradiction between implementation simplicity and performance by providing a structured approach that is both manageable and efficient.
Solution Approach 2:
The patent performs preliminary actions by duplicating the small table S across all PUs before the actual join operation. This preliminary duplication eliminates the need for complex data redistribution during the join process, as each PU already has access to the necessary data from S. This preliminary action significantly improves performance while maintaining implementation clarity.
2Power
If data is distributed across multiple parallel units, then processing capacity increases, but coordination overhead and communication costs increase
Solution Approach 1:
The patent extracts the coordination overhead by isolating the communication-intensive operations. Specifically, only the dangling rows (a small subset of data) are redistributed across PUs, rather than redistributing entire tables or intermediate results. This extraction minimizes network communication and coordination overhead while maintaining the parallel processing capacity benefits.
Solution Approach 2:
The patent applies partial action by duplicating only the small table S across all PUs, rather than duplicating or redistributing the large table L or intermediate join results. This partial duplication provides sufficient data for the join operation at each PU without the excessive communication cost of full data distribution, thus maintaining processing capacity while reducing coordination overhead.
3Productivity
If the small table is duplicated across all parallel units, then join operations can be performed in parallel, but memory and storage requirements increase
Solution Approach 1:
The patent uses partial action by duplicating only the small table S across all PUs, which is necessary for parallel join processing. The large table L remains distributed only where needed, and intermediate results are not fully redistributed. This approach achieves parallel processing efficiency while minimizing the quantity of data stored in memory and on disk, as each PU stores only the duplicated small table and its local portion of the large table.
Data Source
AI summary
A small table S is outer joined to a large table L on a join condition on a database system with a plurality B of parallel units (PUs). S and L are partitioned across the PUs. Each row in S has a unique row-id. Each row of S is duplicated on all PUs to form Sdup. On each PU, dangling rows in S that do not have a match in L under the join condition are identified and the row-ids of the dangling rows are saved in Tredis. Tredis is partitioned across the PUs. P is formed from dangling rows of S whose corresponding entries in Tredis appear in all PUs. A result is produced by unioning P and I. I is formed by inner joining non-dangling rows of S with L. The result is saved.