Partition Injection for Efficient SQL Analytic Query Execution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database query execution plans for analytic functions on multiple measures or dimensions repeat intermediate operations, such as sorting, for each analytic operation, leading to inefficiencies due to serial execution and repeated use of partitioning keys.
Innovation Solution
The introduction of partition creator and partition iterator operators allows for the creation of partitions based on common partition keys, enabling multiple analytic operations to be executed serially on each partition, ignoring partition keys during ranking, thus reducing redundant operations and improving efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If analytic operations are executed separately and serially on a single-row flow, then each analytic operation can be performed independently with its own sorting, but the same intermediate sorting operations are repeated for each analytic operation, leading to inefficiency
Solution Approach 1:
The patent segments the data flow into separate partitioned streams using partitioning operators. Each partition contains rows with identical partition key values, allowing independent processing. This segmentation enables the system to avoid repeating sorting operations across the entire dataset for each analytic function, as sorting is performed once per partition rather than repeatedly on the full single-row flow.
Solution Approach 2:
The patent performs preliminary sorting and partitioning operations before executing multiple analytic functions. By pre-sorting the data within each partition based on the partitioning keys, the system prepares the data in an optimized state that can be reused by subsequent analytic operations. This preliminary action eliminates the need to re-sort data for each analytic function, significantly improving execution efficiency.
2Measurement precision
If intermediate sorting is repeated for each analytic operation on the same partitioning, then each operation maintains its own sorting order, but computational overhead increases due to redundant sorting operations
Solution Approach 1:
The patent merges multiple analytic operations into a unified execution framework that shares common sorting infrastructure. Instead of executing separate sorting operations for each analytic function, the system combines them into a single sorted data flow that serves all analytic operations. This merging eliminates redundant sorting while maintaining the accuracy of ranking results through consistent application of partitioning and sorting logic across all operations.
3Stability of the object's composition
If partitioning keys are used for every analytic operation, then consistent partitioning is maintained across operations, but redundant use of partitioning keys increases computational overhead
Solution Approach 1:
The patent extracts the partitioning operation from each individual analytic function and performs it once at the beginning of the data flow. The partitioning keys are applied once to divide the data into partitions, and subsequent analytic operations work on these pre-partitioned streams without repeatedly applying partitioning logic. This extraction maintains consistent partitioning across all operations while eliminating the redundant computational overhead of repeated partitioning applications.
Data Source
AI summary
Execution plans generated for multiple analytic queries incorporate two new kinds of plan operators, a partition creator and partition iterator. The partition creator and partition iterator operate as a pair. A partition creator operator creates partitions of rows and a partitioning descriptor describing the partitions created. A partition iterator iterates through the partitions based on the partitioning descriptor. For each partition, multiple analytic operators are executed serially, one after the other, on the same rows in the partition. According to an embodiment, partitioning is based on a common grouping or subgrouping of the multiple analytic functions or operators. Columns in the grouping or subgrouping may be ignored when executing each of the multiple analytic operators. Forming execution plans that include partition creator and partition iterator in this way is referred to herein as partitioning injection.