Parallel Recursive Query Execution via Segmented Temporary Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Executing queries with recursive clauses using multiple processes in parallel is bottlenecked by the private buffer cache, limiting the ability to share intermediate results across processes, which restricts parallel execution and leads to inefficiencies.
Innovation Solution
Implementing a system architecture that utilizes a cursor duration temporary table (CDTT) where multiple slave processes manage segments of the temporary table, allowing for parallel execution by distributing and merging results across processes, and using an in-memory CDTT to optimize performance by eliminating the need for merging segments into a master segment.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a private buffer cache is used to store intermediate results in each process, then data integrity and process isolation are maintained, but parallel execution capability is limited and query execution speed decreases
Solution Approach 1:
The patent divides the buffer cache into process-specific segments where each process has its own private buffer region. This segmentation allows multiple processes to execute in parallel while maintaining data isolation and integrity, resolving the contradiction between reliability and productivity.
Solution Approach 2:
The patent introduces an intermediary mechanism (the coordinated buffer cache system with version control) that manages data sharing between processes. This intermediary enables safe parallel access to intermediate results without compromising data integrity, thus improving query execution speed while maintaining reliability.
2Productivity
If multiple processes execute queries in parallel, then query execution speed improves, but coordination overhead and system complexity increase
Solution Approach 1:
Each process independently manages its own buffer cache segment and automatically handles data validation and version checking. This self-service approach reduces the need for complex centralized coordination mechanisms, allowing parallel execution with minimal overhead.
Solution Approach 2:
The patent uses version numbers as parameters to track intermediate results across iterations. By changing the state representation to include version metadata, the system enables automatic coordination without complex control logic, reducing system complexity while maintaining parallel execution capability.
3Productivity
If intermediate results are shared across processes, then parallel execution efficiency improves, but data consistency and isolation are compromised
Solution Approach 1:
The buffer cache is segmented into process-specific regions, allowing controlled sharing of intermediate results. Each process can read from and write to its own segment while maintaining isolation from other processes, ensuring data consistency while enabling parallel execution efficiency.
Solution Approach 2:
The system implements feedback mechanisms through version number checking and result validation. Processes continuously monitor and validate intermediate results before using them, ensuring data consistency is maintained even when sharing results across parallel processes.
Data Source
AI summary
Techniques are described herein for executing queries with a recursive clause using a plurality of processes that execute database operations in parallel. Each process of the plurality of processes either generate or are assigned a segment that is part of a temporary table. For each iteration of the recursive query, work granules are divided up and assigned to each of the plurality of processes. As each respective process produces a portion of a result set for a given iteration, that process appends said portion of the result set to the respective segment that the respective process manages. Each slave process then publishes, to one or more sources, a reference to the newly generated results. During the next iteration, any slave process may access any of the data from the previous iteration.