Cost based optimization of decision support queries using transient views
AssigneeInternational Business Machines Corporation
InventorsNarayana Iyer Subramanian, Shivakumar Venkataraman
Life of this patent
Estimating the term…
Abstract
The present invention discloses a method, apparatus, and article of manufacture for optimizing one or more queries. Initially, redundancies in execution steps for the one or more queries are identified. Then, a new set of equivalent execution steps is created by eliminating redundancies in the execution steps. The new set of equivalent execution steps is used to execute the one or more queries when the use results in efficient query processing.
Claims27
A method of modifying a query plan for improved performance, comprising:
- (a) identifying a plurality of sub-plans within the query plan;
- (b) generating one or more equivalence classes for the query plan, wherein each equivalence class contaims similar ones of the sub-plans;
- (c) generating a transient view to answer all of the sub-plans in an associated one of the equivalence classes, wherein the transient view contains a union of results from all of the sub-plans in the associated one of the equivalence classes;
- (d) utilizing a cost model to identify the transient views that improve the performance of the query plan;
- and
- (e) applying filters to the identified transient views to obtain results for each sub-plan in the associated equivalence class.
An apparatus for optimizing one or more queries, comprising:
- (a) a computer having a data storage device connected thereto, wherein the data storage device stores a database;
- (b) one or more computer programs performed by the computer, for:
- (1) identifying a plurality of sub-plans within the query plan;
- (2) generating one or more equivalence classes for the query plan, wherein each equivalence class contains similar ones of the sub-plans;
- (3) generating a transient view to answer all of the sub-plans in an associated one of the equivalence classes, wherein the transient view contains a union of results from all of the sub-plans in the associated one of the equivalence classes;
- (4) utilizing a cost model to identify the transient views that improve the performance of the query plan;
- and
- (5) applying filters to the identified transient views to obtain results for each sub-plan in the associated equivalence class.
The apparatus of claitn 10, wherein the sub-plans each have a join method property, a join predicates property, an outer plan property, and an inner plan property, and wherein the computer programs further comprise one or more computer programs for using one or more of these properties to determine which of the sub-plans are similar.
An article of manufacture comprising a computer program carrier readable by a computer and embodying one or more instructions executable by the computer to perform method steps of optimizimg one or more queries, the method comprising the steps of:
- (a) identifying a plurality of sub-plans within the query;
- plan;
- (b) generating one or more equivalence classes for the query plan, wherein each equivalence class contains similar ones of the sub-plans;
- (c) generating a transient view to answer all of the sub-plans in an associated one of the equivalence classes, wherein the transient view contains a union of results from all of the sub-plans in the associated one of the equivalence classes;
- (d) utilizing a cost model to identify the transient views that improve the performance of the query plan;
- and
- (e) applying filters to the identified transient views to obtain results for each sub-plan in the associated equivalence class.
Dependencies are inferred from the claim text.