Oracle Corporation
Early grouping optimization for SQL statements by normalizing arithmetic expressions in aggregates

Abstract:

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.