Query Plan Fusing Global Aggregates With Organizing Operations
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems require two passes to compute global reporting aggregates and organizing operations, leading to performance issues such as excessive storage access, data spilling to disk, and increased consumption of time and electricity.
Innovation Solution
A query plan is generated to compute global reporting aggregates and organizing operations in a single pass by using an access structure like a hash table or B-Tree, where each row is processed to update both the organizing operation and cumulative global calculation, allowing for efficient data organization and aggregation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If two-pass query execution is used to compute global reporting aggregates and organizing operations, then accurate results are obtained, but storage access frequency increases and energy consumption rises
Solution Approach 1:
The patent merges the global aggregate computation with the organizing operation into a single integrated process. The query execution engine simultaneously performs grouping/sorting and computes global aggregates in one pass over the data, eliminating the need for separate passes. This is achieved by fusing the aggregate operator with the organizing operator in the query execution plan, allowing both operations to share the same data access and processing resources.
Solution Approach 2:
The query execution engine is designed to perform multiple functions simultaneously - it executes organizing operations (grouping, sorting, duplicate suppression) and computes global reporting aggregates in the same execution pass. This multi-functionality allows the system to achieve both data organization and global aggregation without requiring separate specialized passes, thereby reducing storage access and energy consumption.
2Reliability
If two-pass query execution is used, then complete data processing is achieved, but query execution time increases
Solution Approach 1:
The patent merges the global aggregate computation with the organizing operation into a single integrated process. The query execution engine simultaneously performs grouping/sorting and computes global aggregates in one pass over the data, eliminating the need for separate passes. This is achieved by fusing the aggregate operator with the organizing operator in the query execution plan, allowing both operations to share the same data access and processing resources.
Solution Approach 2:
The system performs preliminary organization of data (grouping, sorting, duplicate suppression) during the first pass while simultaneously computing global aggregates. By completing both organizing and aggregation tasks in the same initial pass, the system avoids the need for a second pass to retrieve and re-process data, thereby significantly reducing query execution time while maintaining complete data processing.
3Quantity of substance
If results are spilled to disk during first pass, then memory constraints are managed, but storage access frequency and energy consumption increase
Solution Approach 1:
The patent merges the global aggregate computation with the organizing operation into a single integrated process. The query execution engine simultaneously performs grouping/sorting and computes global aggregates in one pass over the data, eliminating the need for separate passes. This is achieved by fusing the aggregate operator with the organizing operator in the query execution plan, allowing both operations to share the same data access and processing resources.
4Measurement precision
If two-pass execution is implemented, then accurate global aggregates are computed, but device complexity increases
Solution Approach 1:
The patent merges the global aggregate computation with the organizing operation into a single integrated process. The query execution engine simultaneously performs grouping/sorting and computes global aggregates in one pass over the data, eliminating the need for separate passes. This is achieved by fusing the aggregate operator with the organizing operator in the query execution plan, allowing both operations to share the same data access and processing resources.
Data Source
AI summary
Techniques herein generate a query plan that combines a global reporting aggregate calculation and an organizing operation. A method detects an organizing operation, a group aggregate function, and a global aggregate function within a database statement. The organizing operation specifies organizational activities such as grouping, joining, or sorting rows. The method generates an execution plan that specifies calculating all values in a single pass. For each row, the single pass applies the organizing operation and updates an access structure. The pass updates one of multiple cumulative group calculations based on the group aggregate function and updates a cumulative global calculation based on the global aggregate function. Each cumulative group calculation is associated with some of the access structure. Based on the access structure, result rows that satisfy the database statement are generated. Result rows contain a final result of each group calculation and a final result of the global calculation.