Optimizing nested database queries that include windowing operations
AssigneeTeradata US, Inc.
InventorAhmad Ghazal
Life of this patent
Estimating the term…
Abstract
A nested database query can be optimized by effectively representing a filtering function or operation (e.g., SQL filtering operation) of its outer sub-query by a qualification or qualify operation (e.g., SQL qualification operation) associated with at least one windowing function of at least one of its inner sub-queries. It will be appreciated that this representation can yield a modified query that would be equivalent (e.g., semantically equivalent) to the original nested query as its execution can produce the same results when optimization rules are applied.
Claims20
A computer-implemented method of optimizing a nested database query that includes at least one inner sub-query, at least one outer sub-query, one or more window functions in the at least one inner sub-query, and one or more filtering operations in the at last one outer sub-query, wherein the computer-implemented method comprises:
- determining whether to represent one or more filtering operations of the at least one outer sub-query as one or more corresponding qualification operations associated with the one or more window functions in the at least one inner sub-query;
- and
- effectively representing the one or more filtering operations of the at least one outer sub-query as the one or more corresponding qualification operations associated with the one or more window functions in the at least one inner sub-query when the determining determines to represent the one or more filtering operations as the one or more corresponding qualification operations.
A database system that includes one or more or processors operable to:
- determine whether to represent one or more filtering operations of at least one outer sub-query of a nested query as one or more corresponding qualification operations associated with one or more window functions in at least one inner sub-query of the nested query;
- an
- effectively represent the one or more filtering operations of the at least one outer sub-query as the one or more corresponding qualification operations associated with the one or more window functions in the at least one inner sub-query when the determining determines to represent the one or more filtering operations as the one or more corresponding qualification operations.
A non-transient computer readable storage medium storing at least executable code for optimizing a nested database query that includes at least one inner sub-query, at least one outer sub-query, one or more window functions in the at least one inner sub-query, and one or more filtering operations in the at last one outer sub-query, wherein when the executable is executed by a computer system the executable code:
- determines whether to represent one or more filtering operations of the at least one outer sub-query as one or more corresponding qualification operations associated with the one or more window functions in the at least one inner sub-query;
- and
- effectively represents the one or more filtering operations of the at least one outer sub-query as the one or more corresponding qualification operations associated with the one or more window functions in the at least one inner sub-query when the determining determines to represent the one or more filtering operations as the one or more corresponding qualification operations.
Dependencies are inferred from the claim text.