Correlated Subquery Execution in MPP Databases
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Correlated and multi-row subqueries in distributed databases, such as those with massively parallel processing (MPP) or shared-nothing architectures, face challenges due to data distribution across multiple segments, leading to incorrect results and inefficiencies, as segments lack necessary data or mechanisms to locate and share intermediate results.
Innovation Solution
Incorporating a 'broadcast' operator into the execution plan to make intermediate results from each database segment available to all others, and optimizing the subquery plan by removing motion nodes, materializing results locally, and applying correlated filters to ensure correct and efficient execution of correlated subqueries across distributed databases.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data is distributed across multiple segments in an MPP database, then parallel processing capability is improved, but the ability to execute correlated subqueries deteriorates because segments lack necessary data
Solution Approach 1:
The patent introduces a coordinator node as an intermediary that manages correlated subquery execution across distributed segments. The coordinator collects intermediate results from all segments, performs the correlation operation centrally, and distributes results back. This mediator approach allows parallel processing to continue while ensuring correct correlated subquery execution through centralized coordination.
Solution Approach 2:
The patent segments the correlated subquery execution into distinct phases: local execution on each database segment to generate intermediate results, collection of these results by the coordinator, and final correlation operation. This segmentation allows parallel processing at the local execution stage while ensuring correctness at the final correlation stage.
2Adaptability or versatility
If segments execute queries independently in a distributed database, then system autonomy is improved, but data location and sharing mechanisms deteriorate
Solution Approach 1:
The patent implements a feedback mechanism where segments execute queries independently and return intermediate results to the coordinator. The coordinator collects this feedback from all segments, processes the correlated subquery, and distributes the final results back. This feedback loop maintains segment autonomy while ensuring intermediate results are collected and shared as needed.
3Ease of operation
If motion nodes are kept in the execution plan, then data movement flexibility is improved, but execution efficiency deteriorates due to redundant data transfer
Solution Approach 1:
The patent extracts and removes motion nodes from the execution plan for correlated subqueries. By eliminating these data movement operations, the system avoids redundant data transfer between segments while maintaining the necessary flexibility through the coordinator's centralized result collection and distribution mechanism.
Data Source
AI summary
The invention enables a correlated or multi-row subquery (CSQ) to be performed on distributed MPP and shared-nothing databases by broadcasting intermediate results, prior to a correlation operation, from subquery execution on one segment to all other segments in the distributed database so that the respective CSQs of each segment will have access to the necessary results to permit correct execution of the CSQ. Additionally, the intermediate results are saved to disk to avoid the necessity of replicating the same intermediate results multiple times during execution of a subquery plan.