Parallel Window Function Evaluation via Extended Data Distribution Key
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing relational database management systems face scalability issues when evaluating ranking and cumulative window functions due to a low number of distinct values of partition-by keys, limiting the number of parallel executing processes and resulting in inefficient use of resources.
Innovation Solution
A scalable computation algorithm is introduced that uses an extended data distribution key, incorporating order-by keys and temporary distribution variables, to distribute input data among multiple parallel processes, allowing for a larger number of parallel executing processes and improving resource utilization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data is distributed using only partition-by keys, then the evaluation of window functions can be performed in parallel, but the number of parallel processes is limited by the low number of distinct partition-by key values
Solution Approach 1:
The patent introduces a new dimension to the data distribution key by combining partition-by keys with order-by keys. This creates an extended data distribution key that has more distinct values, enabling distribution across more parallel processes. The extension from partition-by keys alone to partition-by keys plus order-by keys represents a dimensional expansion that resolves the limitation on parallel process count.
Solution Approach 2:
The patent segments the data distribution task into two components: the base partition-by keys and the additional order-by keys. By segmenting the distribution key formation, the system can leverage both partition-by keys for initial grouping and order-by keys for further subdivision, creating more granular parallel execution units without compromising the correctness of window function evaluation.
2Loss of energy
If more parallel processes are used to evaluate window functions, then resource utilization improves, but the system requires an extended data distribution mechanism beyond simple partition-by key distribution
Solution Approach 1:
The extended data distribution key serves multiple functions simultaneously: it maintains the partitioning semantics of partition-by keys for correctness, incorporates order-by keys for additional parallelism, and provides a unified distribution mechanism that works for both ranking and cumulative window functions. This multi-functionality allows the same mechanism to achieve both correctness and improved resource utilization.
Solution Approach 2:
The patent introduces the extended data distribution key as an intermediary between the query processing requirements and the parallel execution framework. This intermediary structure translates the need for both partition-by semantics and order-by semantics into a single distribution mechanism that enables fine-grained parallelism while maintaining evaluation correctness.
Data Source
AI summary
According to one aspect of the invention, for a database statement that specifies evaluating ranking or cumulative window functions, an execution strategy based on an extended data distribution key may be used for the database statement. In the execution strategy, each sort operator of multiple parallel processing sort operators computes locally evaluated results of a ranking or cumulative window function based on a subset of rows in all rows used to evaluate the database statement, and sends the first and last rows' locally evaluated results to a query coordinator. The query coordinator consolidates the locally evaluated results received from the multiple parallel processing sort operators and sends consolidated results to the sort operators based on their respective demographics. Each sort operator completes full evaluation of the ranking or cumulative window functions based at least in part on one or more of the consolidated results provided by the query coordinator.