Database Aggregates on Distinct Values via Spool Files
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face inefficiencies and inaccuracies when computing aggregates on distinct attribute values for group-by operations involving multiple levels of grouping sets, as conventional methods fail to produce accurate results when a DISTINCT aggregate is specified.
Innovation Solution
The database system employs a two-step process involving the creation of a spool file containing distinct attribute values, allowing higher-level group-bys to be computed from this spool rather than the original table, and reusing intermediate spool files to efficiently calculate aggregates across multiple grouping sets.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If conventional group-by computation methods are used for multiple grouping sets, then computation efficiency is improved by calculating higher-level group-bys from lower-level results, but accuracy deteriorates when DISTINCT aggregates are specified
Solution Approach 1:
The patent segments the computation process into distinct phases: first computing lower-level group-bys with DISTINCT aggregates, storing results in spool files, then using these spool files as input for higher-level group-bys. This segmentation allows each phase to maintain accuracy while enabling efficient reuse of intermediate results.
Solution Approach 2:
The patent performs preliminary computation of lower-level group-bys with DISTINCT aggregates and stores them in spool files before computing higher-level group-bys. This preliminary action ensures that distinct value aggregates are accurately computed once and reused, maintaining accuracy while improving efficiency.
2Measurement precision
If DISTINCT aggregates are computed for each grouping set from the base table, then accuracy is maintained, but computation time increases significantly
Solution Approach 1:
The patent merges the computation of multiple grouping sets by using spool files containing DISTINCT aggregate results from lower-level group-bys as input for higher-level group-bys. This combining approach computes DISTINCT aggregates once and reuses them across multiple grouping sets, reducing redundant computation while maintaining accuracy.
Solution Approach 2:
The patent maintains continuity of useful action by keeping DISTINCT aggregate results in spool files and continuously reusing them for higher-level group-bys. This eliminates repeated computation of the same DISTINCT aggregates, significantly reducing computation time while preserving accuracy.
3Productivity
If spool files are created and reused for multiple grouping sets, then computation efficiency is improved, but system resource usage increases
Solution Approach 1:
The patent creates spool files as copies of DISTINCT aggregate results from lower-level group-bys, which are then reused as input for higher-level group-bys. This copying mechanism enables efficient reuse of intermediate results without requiring the base table to be repeatedly scanned, improving computation efficiency while managing memory resources through structured spool file creation.
Data Source
AI summary
A database system computes an aggregate on distinct values of at least one attribute for each of plural groups specified by multiple levels of grouping sets. Distinct values of the at least one attribute are identified and stored in a first table. The first table is used to compute aggregates for groups specified by a first grouping set and for groups specified by a second grouping set.