Patent ID: 7693820

Claim:
A method of optimizing a database query or batch of queries, the method to be run on a computing system, the method comprising: identifying sharable subexpressions in a query, wherein the step of identifying comprises: determining a table signature associated with each expression of the query, wherein the table signature for a particular expression does not contain individual predicates and group-by columns of the particular expression; storing the table signature associated with said each expression; and identifying sharable subexpressions as expressions having the same table signature; generating two or more transient view candidates covering sets of sharable subexpressions; selecting from among the transient view candidates those candidates that are beneficial for execution of the query, wherein the step of selecting further comprises: applying heuristic rules to eliminate one or more of the generated transient view candidates to reduce the set of transient view candidates, wherein the step of applying heuristic rules further comprises: applying heuristic rules to eliminate any identified transient view candidates the use of which is not likely to improve execution performance of the optimized query plan, the heuristic rules comprising: eliminating any transient view candidate for which the estimated total cost of evaluating its potential consumers does not exceed a minimum threshold portion of the estimated total cost for evaluating the query; eliminating any potential consumer expression from consideration when constructing a candidate transient view if the estimated cost of evaluating the expression without use of the transient view candidate is less than the estimated cost of evaluating the expression by scanning the transient view candidate; eliminating any transient view candidate wherein a transient view candidate Vnew represents the merger of a first transient view candidate V 1 and a second transient view candidate V 2 and wherein the estimated cost of evaluating the potential consumers using Vnew is greater than the estimated cost of evaluating them using V 1 and V 2 ; eliminating any transient view candidate wherein the maximum benefit for use of the transient view candidate in evaluating the query does not exceed a minimum threshold portion of the estimated total cost for evaluating the query; and eliminating any transient view candidate if it is contained by a wider view candidate with the same potential consumers and where the result size of the transient view candidate exceeds a minimum threshold portion of the size of the wider candidate wherein a view V 1 is wider than another view V 2 if V 1 references the same tables as V 2 plus at least one additional table; and generating an optimized query execution plan including the selected transient views and including operators to materialize the selected transient views for re-use during execution of the optimized query execution plan.