Query Rewriting Using Window Functions to Eliminate Subqueries

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Inefficient query execution plans are generated due to subqueries in the HAVING clause of complex queries that can be subsumed by the outer query, leading to unnecessary table accesses and join operations, often caused by automated query generation or developer unawareness of query transformation intricacies.

Innovation Solution

Rewriting queries to eliminate subqueries from HAVING clauses by using algebraic aggregate window functions, creating inline views that compute aggregate functions over a range, and applying predicates to reference these views, thereby eliminating duplicative join operations and table accesses.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If subqueries are used in HAVING clauses for complex queries, then query correctness is maintained, but query execution efficiency deteriorates due to unnecessary duplicative table accesses and join operations

Engineering Contradiction:

Improvequery execution efficiencyVSAvoidduplicative table accesses and join operations

Core Design Contradiction:

ProductivityVSLoss of energy

Solution Approach 1:

The patent merges the subquery and outer query into a single unified query structure by eliminating the subquery from the HAVING clause. The window function applies the aggregate calculation directly to the outer query's result set, combining what were previously separate execution units into one efficient operation that avoids duplicative table accesses and joins.

Inventive Principle:

Principle #5Merging (Combining)

Solution Approach 2:

The patent extracts the subquery from the HAVING clause entirely, removing the redundant computational layer. By replacing the subquery with a window function that operates on the outer query's grouped results, the unnecessary table accesses and join operations are eliminated while preserving the intended filtering logic.

Inventive Principle:

Principle #2Taking out (Extraction)

2Ease of operation

If automated query generation tools are used, then query development ease is improved, but query optimization quality deteriorates due to inability to perform complex query transformations

Engineering Contradiction:

Improvequery development easeVSAvoidquery execution efficiency

Core Design Contradiction:

Ease of operationVSProductivity

Solution Approach 1:

The patent enables query optimization to occur automatically through the database system's query processing engine. The window function mechanism allows the system to self-optimize by recognizing and eliminating redundant subquery structures in HAVING clauses, performing the transformation without requiring manual intervention from developers or complex automated rewriting tools.

Inventive Principle:

Principle #25Self-service

Data Source

PatentUS7945560B2Technique for removing subquery in group by—having clauses using window functions

Publication Date: 2011.05.17 ORACLE INT CORP

AI summary

Methods for transforming a query to remove redundant subqueries in HAVING clauses are provided. The methods provided transform queries that contain subqueries in HAVING clauses with tables and join conditions and filter conditions equal to tables, join conditions and filter conditions in outer query to queries that eliminate the original subquery and retain the original outer query with a single inline view using window functions. Whether this transformation can be performed depends on which tables and join and filter conditions are in the outer query and the subquery. The transformation eliminates duplicative table accesses and join operations from queries.