US 8,135,702 B2 · Eliminating unnecessary statistics collections for query optimization

Abstract

A method and system for eliminating unnecessary statistics collections for query optimization in a database stored on a computer. Statistics are unnecessary when a re-generated query execution plan that does not use the statistics is equivalent to an original query execution plan that uses the statistics. To determine this, an original query execution plan is created for each query in a specified workload using the statistics in the database. A search is performed of the statistics in order to enumerate one or more candidate sets of statistics to be eliminated. One or more of the candidate sets of statistics are removed from consideration prior to creating the re-generated query execution plan for each query in the specified workload. The re-generated query execution plan, which was created with one or more of the candidate sets of statistics removed from consideration, is compared with the original query execution plan, which was created using the statistics, in order to determine whether the re-generated query execution plan and the original query execution plan are equivalent. The one or more of the candidate sets of statistics removed from consideration are identified as unnecessary when the re-generated query execution plan and the original query execution plan are equivalent.

Claims22

Dependent claims

A computer-implemented method of managing statistics collections stored on a computer readable storage medium and used to optimize execution of queries associated with a database, the computer-implemented method comprising:

(a) determining whether one or more of the statistic collections are unnecessary for optimization of at least one query associated with the database, by determining whether a re-generated query execution plan, for the at least one query, that does not use the one or more of the statistics collections is equivalent to an original query execution plan for the at least one query, that uses the one or more statistics collections;
and
eliminating the one or more statistic collections from the computer readable storage medium when the determining (a) determines that the one or more statistic collections are unnecessary.

A non-transient computer readable storage medium storing at least executable computer code to manage statistics collections used to optimize execution of queries associated with a database, wherein the executable computer code includes:

executable computer code operable to (a) determine whether one or more of the statistic collections are unnecessary for optimization of at least one query associated with the database, by determining whether a re-generated query execution plan that does not use the one or more of the statistics collections is equivalent to an original query execution plan for the at least one query that uses the one or more statistics collections;
and
executable computer code operable to eliminate the one or more statistic collections when the determining (a) determines that the one or more statistic collections are unnecessary.

Dependencies are inferred from the claim text.

Eliminating unnecessary statistics collections for query optimization

Life of this patent

Eliminating unnecessary statistics collections for query optimization

Life of this patent

Abstract

Claims22