Dependent Grouping Column Rewrite for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database query processing techniques face inefficiencies due to the inclusion of non-relevant GROUP BY columns, which increase compute cost and hash collisions, particularly in queries with functional dependencies where additional grouping columns do not affect the grouping operation.
Innovation Solution
The Dependent Grouping Column Rewrite technique transforms dependent grouping columns into aggregation columns using non-blocking aggregation functions, thereby simplifying the grouping operation and reducing unnecessary processing overhead by excluding these columns from hash computations and key matching.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If additional GROUP BY columns are included in queries with functional dependencies, then query completeness is improved, but compute cost and hash collisions increase
Solution Approach 1:
The patent extracts and identifies functional dependencies between grouping columns using data dictionary information. By separating dependent columns from independent columns, the system removes unnecessary columns from hash key computations while preserving query correctness. This extraction principle directly resolves the contradiction by eliminating redundant processing elements that cause hash collisions without affecting query completeness.
Solution Approach 2:
The patent changes the parameter set for hash key computation by dynamically determining which grouping columns should be included based on functional dependency analysis. Instead of using all grouping columns uniformly, the system adjusts the effective parameter set (hash key columns) by excluding dependent columns, thereby reducing compute cost and hash collisions while maintaining query accuracy.
2Measurement precision
If more GROUP BY columns are used in hashing operations, then grouping accuracy is improved, but memory pressure increases
Solution Approach 1:
The patent extracts the essential grouping columns needed for accurate hash-based grouping by analyzing functional dependencies. By removing dependent columns from the hash key construction, the system reduces the amount of data stored in hash buckets and memory structures, directly lowering memory pressure while preserving grouping accuracy through the remaining independent columns.
3Reliability
If all grouping columns are included in hash key computations, then grouping correctness is maintained, but processing overhead increases
Solution Approach 1:
The patent extracts functional dependency relationships from the data dictionary to identify which grouping columns are redundant for the specific query. By taking out dependent columns from hash key computations, the system reduces processing overhead and hashing operations without compromising grouping correctness, as the independent columns alone suffice to maintain proper groupings.
Solution Approach 2:
The patent performs preliminary analysis of functional dependencies before executing the grouping operation. By pre-determining which columns are necessary for hash key computation based on the data dictionary, the system avoids unnecessary hashing and processing during query execution, thereby reducing processing time while ensuring grouping correctness is maintained through the selected columns.
Data Source
AI summary
Techniques described herein proposes a Dependent Grouping Column Rewrite that simplifies a grouping operation by excluding dependent grouping columns from a group by clause. A non-blocking aggregation function is applied to dependent grouping columns in at least select clauses. Such a simplified grouping operation reduces memory pressure, speeds up grouping operations, and saves compute time, as grouping columns that have no additional effect on the grouping operation are excluded from processing (e.g., hashing and key matching).