Cost based optimization of decision support queries using transient views

Claim 3

The method of claim 1 , further comprising determining whether to materialize the transient view.

Claim 4

The method of claim 1 , wherein slimlar ones of the sub-plans contained in an equivalence class are identified by one or more properties.

Claim 5

The method of claim 3 , wherein the properties are selected from a property list comprised of one or more of the following: tables in the plan, columns selected, join predicates, selection predicates, aggregation functions, columns in a GROUP BY list, data sources, a cost of executing the plan, and a number of result rows.

Claim 6

The method of claim 4 , further comprising: generating a query that represents the transient view from the property list of the transient view; optimizing the query; and obtaining an execution plan, cost, and cardinality for the transient view.

Claim 7

The method of claim 1 , further comprising using the cost model to decide whether to materialize the transient view.

Claim 8

The method of claim 1 , wherein the sub-plans each have a scan type property, a table scanned property, a simple predicates property, and a columns selected property, further comprising the step of using one or more of these properties to determine which of the sub-plans are similar.

Claim 9

The method of claim 1 , wherein the sub-plans each have a join method property, a join predicates property, an outer plan property, and an inner plan property, and further comprising the step of using one or more of these properties to determine which of the sub-plans are similar.

Claim 10

The method of claim 1 , wherein the sub-plans each have an input plan property, and further comprising the step of using this property to determine which of the sub-plans are similar.

Claim 13

The apparatus of claim 10 , wherein the computer programs further comprise one or more computer programs for determning whether to materialize the transient view.

Claim 14

The apparatus of claim 10 , wherein similar ones of the sub-plans contained in an equivalence class are identified by one or more properties.

Claim 15

The apparatus of claim 12 , wherein the properties are selected from a property list comprised of one or more of the following: tables in the plan, columns selected, join predicates, selection predicates, aggregation functions, columns in a GROUP BY list, data sources, a cost of executing the plan, and a number of result rows.

Claim 16

The apparatus of claim 13 , wherein the computer programs further comprise one or more computer programs for: generating a query that represents the transient view from the property list of the transient view; optimizing the query; and obtaining an execution plan, cost, and cardinality for the transient view.

Claim 17

The apparatus of claim 10 , wherein the computer programs further comprise one or more computer programs for using the cost model to decide whether to materialize the transient view.

Claim 18

The apparatus of claim 10 , wherein the sub-plans each have a scan type property, a table scanned property, a simple predicates property, and a columns selected property, 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.

Claim 21

The apparatus of claim 10 , wherein the sub-plans each have an input plan property, and wherein the computer programs further comprise one or more computer programs for using this property to determine which of the sub-plans are similar.

Claim 22

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.

Claim 23

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.

Claim 24

The article of manufacture of claim 19 , further comprising determining whether to materialize the transient view.

Claim 25

The article of manufacture of claim 19 , wherein similar ones of the sub-plans contained in an equivalence class are identified by one or more properties.

Claim 26

The article of manufacture of claim 21 , wherein the properties are selected from a property list comprised of one or more of the following: tables in the plan, columns selected, join predicates, selection predicates, aggregation functions, columns in a GROUP BY list, data sources, a cost of executing the plan, and a number of result rows.

Claim 27

The article of manufacture of claim 22 , further comprising: generating a queryn that represents the transient view from the property list of the transient view; optimzing the query; and obtaining an execution plan, cost, and cardinality for the transient view.

Claim 28

The article of manufacture of claim 19 , further comprising using the cost model to decide whether to materialize the transient view.

Claim 29

The article of manufacture of claim 19 , wherein the sub-plans each have a scan type property, a table scanned property, a simple predicates property, and a columns selected property, further comprising the step of using one or more of these properties to determine which of the sub-plans are similar.

Claim 30

The article of manufacture of claim 19 , wherein the sub-plans each have a join method property, a join predicates property, an outer plan property, and an inner plan property, and further comprising the step of using one or more of these properties to determine which of the sub-plans are similar.

Claim 31

The article of manufacture of claim 19 , wherein the sub-plans each have an input plan property, and further comprising the step of using this property to determine which of the sub-plans are similar.