Hash-Based Rollup with Passthrough for Cluster Databases
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional algorithms for executing the GROUP BY ROLLUP command in cluster database systems are inefficient for large data sets, requiring significant memory and computational resources due to duplication of rows and sorting of entire datasets.
Innovation Solution
A hash-based rollup system with a pass-through optimization that uses preaggregation and distribution of data across multiple computing units, reducing memory usage and computational load by aggregating data in a distributed manner without duplicating rows and sorting.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional GROUP BY ROLLUP algorithms duplicate each input row for each output grouping set and sort the entire input data set, then the grouping operation can be completed, but memory usage and computational resources increase significantly
Solution Approach 1:
The patent segments the GROUP BY ROLLUP operation into multiple passes, where each pass handles a specific aggregation level. Instead of duplicating rows for all grouping sets simultaneously, the system processes one grouping level at a time, significantly reducing the memory footprint required for row duplication and sorting operations.
Solution Approach 2:
The patent performs preliminary aggregation in a first pass through the input data, creating intermediate aggregated results that are then used in subsequent passes. This preliminary action eliminates the need to keep all original input rows in memory during later aggregation stages, reducing overall memory requirements.
2Measurement precision
If traditional algorithms duplicate rows and sort the entire dataset for GROUP BY ROLLUP, then accurate grouping results are achieved, but execution time increases
Solution Approach 1:
By dividing the rollup operation into sequential passes, each handling a specific aggregation depth, the system avoids the computational overhead of sorting the entire dataset multiple times. Each pass works with progressively smaller datasets, maintaining grouping accuracy while reducing total execution time.
Solution Approach 2:
The preliminary aggregation pass pre-computes intermediate results that satisfy the grouping requirements for subsequent passes. This eliminates redundant sorting and processing in later stages, preserving measurement precision while significantly reducing execution time.
3Productivity
If traditional GROUP BY ROLLUP processes large datasets with row duplication and sorting, then complete aggregation is achieved, but computational load becomes infeasible
Solution Approach 1:
The patent segments the computational workload into manageable passes, where each pass performs a subset of the total aggregation work. This segmentation reduces the peak computational complexity from O(n²) or O(n log n) for full dataset sorting to linear or near-linear complexity per pass, making large dataset processing feasible.
Solution Approach 2:
The preliminary aggregation pass performs essential preprocessing that simplifies subsequent computational tasks. By pre-grouping and pre-aggregating data, the system reduces the complexity of later passes from complex sorting and comparison operations to simpler lookup and accumulation operations.
Data Source
AI summary
A system includes a plurality of computing units. A first computing unit of the plurality of computing units comprises: a communication interface configured to receive an indication to roll up data in a data table; and a processor coupled to the communication interface and configured to: build a preaggregation hash table based at least in part on a set of columns and the data table by aggregating input rows of the data table; for each preaggregated hash table entry of the preaggregated hash table: provide the preaggregated hash table entry to a second computing unit of the plurality of computing units based at least in part on a distribution hash value; receive a set of received entries from computing units of the plurality of computing units; and build an aggregation hash table based at least in part on the set of received entries by aggregating the set of received entries.