Parallel Shared Cursor Model for SQL Execution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing parallel SQL execution systems face challenges in managing and tuning parallel queries due to the complexity of expressing and communicating tasks to slave processes, with the slave SQL approach requiring extensive specialized support and being error-prone, especially during the unparsing of row-source trees and generation of slave SQL statements.
Innovation Solution
The Parallel Shared Cursor (PSC) model shares a single cursor containing the complete execution plan among all participants, eliminating the need for slave SQL and reducing the complexity of managing parallel queries by allowing slaves to access the same compiled information as the query coordinator, thereby simplifying the architecture and improving manageability.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If slave SQL approach is used to express and communicate tasks to slave processes, then parallel query execution is enabled, but the system complexity increases and error rates increase due to extensive specialized support and unparsing complexity
Solution Approach 1:
The patent extracts the slave SQL generation and unparsing complexity from the system by using a shared cursor approach where the query coordinator shares its cursor with slave processes. This eliminates the need for complex slave SQL generation and unparsing operations, reducing system complexity while maintaining parallel execution capability.
Solution Approach 2:
The shared cursor serves multiple functions: it is used by the query coordinator for query planning and optimization, and shared with slave processes for execution. This universal cursor object eliminates the need for separate slave SQL representations, reducing overall system complexity.
2Productivity
If slave SQL approach is used to communicate tasks to slaves, then parallel execution is achieved, but manageability and monitoring become more difficult due to multiple cursors and intermediate languages
Solution Approach 1:
The patent merges the cursor objects into a single shared cursor that is accessible to both the query coordinator and slave processes. This consolidation eliminates the need to manage multiple cursors and intermediate languages, significantly improving manageability and monitoring of parallel queries.
3Reliability
If multiple cursors are maintained for different participants, then execution plans can be shared, but code maintenance becomes more complex and error-prone
Solution Approach 1:
The patent merges multiple cursor instances into a single shared cursor object that is referenced by both the query coordinator and slave processes. This eliminates duplication of cursor management code, reducing maintenance complexity and errors while maintaining reliable execution plan sharing.
Data Source
AI summary
Techniques are provided for executing query statements, or portions thereof, in parallel without using slave SQL to communicate to each slave the operations to be performed by the slave. Instead of generating one cursor for the query coordinator (QC) based on the original statement, and separate cursors for each DFO based on the slave SQL, the techniques involve sharing the cursor that is generated from the original statement among the various participants involved in the parallel execution of the operations specified in the original statement.