International Business Machines Corporation

Lynnette Elaine Carston

Brian Robert Muras

Andrew Peter Passe

Jeffrey Wayne Tenner

Mark John Anderson

Alwood Patrick Williams, Iii

Samuel J Miller

Craig Stephen Aldrich

Thomas James Schreiber

Gary E Rohret

In an embodiment, a recursive query is received that comprises a first select statement with a seed select statement and a second select statement with a recursive reference, wherein the recursive query further identifies at least two columns in at least one table, wherein the at least two columns have parent-child relationships represented by nodes in a graph, wherein the graph represents the organization of values in rows in the at least one table. A target recursion depth is calculated for the graph based on a fanout of the graph. In an embodiment, the target recursion depth is calculated by summing the fanout at each recursion depth of the graph multiplied by a number of nodes at each recursion depth of the graph. An estimated number of rows that the recursive query will return is estimated based on the target recursion depth.

Estimating rows returned by recursive queries using fanout

Embodiments of the invention provide a method of optimizing a query, including determining an execution plan for use in executing the query, based on a monetary cost of the energy needed to execute the query. A query optimization component may determine a plurality of execution plans for the query. The query optimization component may then select one of the plans to use in executing the query based on the monetary cost calculated for the plan.

Energy monetary cost aware query optimization

Disclosed is an apparatus, method, and program product for performing efficient database access plan caching. Unlike prior art mechanism that automatically discard stored access plans whenever the need for a different access plans is detected, stored plans are intelligently retained despite the handling of one or more infrequent queries that call for the use of a different plan.

Data management system that provides intelligent access plan caching

Growing community of inventors

Rochester, MN, United States of America

Lynnette Elaine Carston