Single Scan First Last Aggregation for Database Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Complex database queries often require multiple scans of database tables for FIRST and LAST aggregations over multiple keyfigures, leading to increased processor, memory, and power consumption due to the need for multiple calculation nodes and threads competing for CPU resources.
Innovation Solution
A method where a calculation engine performs FIRST and LAST aggregations in a single scan of the database table by detecting groups and keyfigures using hash values, allowing for optimized query execution and reducing the number of scans required.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If multiple table scans are performed for FIRST and LAST aggregations over multiple keyfigures, then aggregation accuracy is maintained, but processor usage and memory consumption increase
Solution Approach 1:
The patent combines multiple FIRST and LAST aggregation operations over different keyfigures into a single table scan. Instead of performing separate scans for each aggregation, the system executes one unified scan that computes all required aggregations simultaneously, thereby maintaining accuracy while reducing processor usage and memory consumption.
Solution Approach 2:
The single table scan is designed to perform multiple aggregation functions (FIRST and LAST aggregations over multiple keyfigures) in one pass. This multi-functional approach allows the same scanning operation to serve multiple aggregation purposes, eliminating the need for separate specialized scans for each keyfigure.
2Reliability
If multiple calculation nodes and threads are used for complex aggregations, then aggregation completeness is ensured, but lock contention increases and runtime increases
Solution Approach 1:
The patent merges multiple calculation nodes into a single unified calculation process. By consolidating the computation of FIRST and LAST aggregations for all keyfigures into one calculation pass, the system eliminates the need for multiple threads to compete for locks, thereby maintaining aggregation completeness while reducing runtime through reduced lock contention.
3Measurement precision
If multiple table scans are performed for FIRST and LAST aggregations, then aggregation accuracy is maintained, but memory consumption increases
Solution Approach 1:
The patent combines multiple aggregation operations into a single table scan that processes all keyfigures in one pass. This approach maintains aggregation accuracy by ensuring all data is processed correctly, while reducing memory consumption by avoiding the need to store and process intermediate results from multiple separate scans.
Data Source
AI summary
Methods and apparatus, including computer program products, are provided for first and last aggregation. In one aspect, there is provided a method, which may include receiving, by a calculation engine, a query; detecting, by the calculation engine, whether the query includes a first aggregation and/or a last aggregation over at least one group and at least one keyfigure; optimizing the received query, when the detecting indicates the received query includes the first aggregation and/or the last aggregation, wherein the optimizing further comprises initiating execution of the received query by at least: performing a single read of a table, detecting, from the single table read, at least one group, and indicating, in the detected at least one group, the first aggregation in the at least one keyfigure and/or the last aggregation in the at least one keyfigure; and returning, for the at least one detected group, the indicated first aggregation and/or the indicated second aggregation. Related apparatus, systems, methods, and articles are also described.