SQL Query Group-By Normalization for Arithmetic Expressions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems face limitations in applying early grouping transformations to SQL queries due to restrictive rules that treat all database objects in aggregate expressions as an indivisible group, preventing wider applicability and reducing the potential states considered for query rewriting, especially in cases with complex arithmetic expressions or all database objects being used in aggregate functions.
Innovation Solution
Introducing relaxed-C states that allow some tables in aggregate expressions to be included in the GBP view while others are not, enabling normalization of arithmetic expressions and applying aggregate function-specific query modification rules to split tables between the GBP view and the outer query, thereby increasing the applicability of GBP transformations and generating more potential states for cost-based optimization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If all database objects in aggregate expressions are treated as an indivisible group, then query transformation rules are simpler to implement, but the applicability of GBP transformations is limited and fewer potential states are considered
Solution Approach 1:
The patent segments the set of database objects referenced in aggregate expressions into two distinct groups: those included in the GBP view and those excluded. This segmentation allows the query transformation framework to consider more potential states by independently deciding which objects to include, rather than treating all objects as an indivisible group. The segmentation enables GBP transformations to be applied to a wider range of queries while maintaining manageable complexity through systematic state generation.
2Adaptability or versatility
If complex arithmetic expressions are present in aggregate functions, then query semantics become more expressive, but GBP transformations cannot be applied
Solution Approach 1:
The patent extracts database objects from complex arithmetic expressions within aggregate functions and treats them as separate entities that can be independently included or excluded in the GBP view. By taking out the database objects from the arithmetic expressions, the system can analyze and transform queries with complex arithmetic without being blocked by the presence of such expressions. This extraction approach maintains semantic correctness while enabling GBP transformations.
3Productivity
If more potential states of GBP transformation are considered, then better query optimization is achieved, but the cost-based query transformation framework becomes more computationally expensive
Solution Approach 1:
The patent performs preliminary actions by systematically generating potential GBP states based on segmented database objects before the cost-based optimization process. By pre-identifying which database objects can be included in the GBP view and generating the corresponding potential states in advance, the framework reduces the computational burden during the actual optimization phase. This preliminary segmentation and state generation enables more states to be considered without proportionally increasing the overall optimization time.
Data Source
AI summary
According to embodiments, a database server instance may generate group-by placement (GBP) states that define potential GBP transformations for a query in which some aggregate tables are included in the GBP view and other aggregate tables are not. Such states are referred to herein as relaxed-C states. To rewrite a query based on a selected relaxed-C GBP state such that the rewritten query is semantically equivalent to the original query, any arithmetic expression within an aggregate expression of the query is normalized into terms, and the query rewrite is implemented on a term-specific basis. Specifically, aggregate function-specific query modification rules are applied to each term of the normalized arithmetic expression. The applied query modification rules dictate what is included in the GBP view and what is included in the outer portion of the rewritten query to ensure that the rewritten query is semantically equivalent to the original query.