V. Harinarayan, A. Gupta. Generalized projections: a powerful queryoptimization technique. Stanford University CS-TN-94-14, 1994.  Home/Search   Document Details and Download   Summary   Related Articles   Check

....Another line of work has proceeded in an orthogonal direction, introducing execution plans which correspond to alternative structures of nesting. In particular, these approaches consider the possibilities of merging query blocks, denesting queries, and commuting aggregation blocks with joins [Day87, GW87, Kim82, Mur92, PHH92, YL94, HG94]. In this paper we propose an approach which unifies and generalizes the approaches mentioned above. We apply the selection propagation idea to certain data dependencies that are implicit in aggregation blocks. Propagation of SQL predicates [MFPR90a, MFPR90b, MPR90, LMS94] is a special case of ....

....dependencies that are implicit in aggregation blocks. Propagation of SQL predicates [MFPR90a, MFPR90b, MPR90, LMS94] is a special case of propagation of these dependencies. At the same time, propagating these 1 dependencies can produce execution plans with alternative nesting structure, as in [Day87, GW87, Kim82, Mur92, PHH92, YL94, HG94]. In addition to expressing in a common framework previously proposed query transformations which seemed unrelated, our approach incorporates naturally general data dependencies that may be given in the database schema. It extends transformations which commute joins with aggregation operators and ....

[Article contains additional citation context not shown here]

V. Harinarayan, A. Gupta. Generalized projections: a powerful queryoptimization technique. Stanford University CS-TN-94-14, 1994.

.... If X is used in P Replace X = agg(Y ) No effect add X 0 = agg(Y ) by X = agg(X 0 ) gby(X) 3 Add X as gby attr nothing nothing Table 3: How to push GPs 3 An aggregate component of GP P can appear as an aggregate component in Q even when the component appears as a groupby attribute in node N [HG94]. We omit discussing these further optimizations to retain clarity of the discussion. Component What to add Effect on P form to P to get Q X = Y ) Y 0 = Y ) Replace X = Y ) by X = Y 0 ) X = max(Y ) Y 0 = max(Y ) Replace X = max(Y ) by X = max(Y 0 ) X = min(Y ) Y 0 = min(Y ) ....

.... Gamma S. Thus, aggregate component (B) when pushed past a difference, becomes groupby component B. Similarly, it is not possible to push any of the aggregate computations , max, min, or sum, past difference nodes. However, it is possible to push the aggregates count and avg past difference [HG94]. Set difference is similarly handled. Consider the union of relations R(A; B) and S(A; B) Let the GP A; B) be present above the union node R [ S. That is, in R [ S we need the maximum value of B for each value of A. The maximum values B for each A, from relations R and S can be used to ....

Venky Harinarayan and Ashish Gupta. Generalized Projections: a Powerful QueryOptimization Technique. Technical report, Department of Computer Science, Stanford.

....min in the outer GP since there are no other aggregate computation, to get A;X;Y =min(C) A;C;X=max(B) By cascading ( computations we retain correctness but we do not remove as many tuples as we potentially could. The ; operators and their algebra are explained in greater detail in [HG94]. 4.3 GP Push down 4.3.1 Duplicate Insensitive GPs In the ensuing discussion, we consider pushing GPs down query trees and examine the interaction of GPs with the different types of nodes in the query tree. Selection Nodes: As mentioned in Section 4.1, duplicate insensitive GPs behave ....

....retaining all distinct values of B makes the max(B) computation possible. 2 In this paper, in the interest of clarity, we do not consider aggregation computations on multiple attributes, for example sum(B C) Redundant components are dropped similarly in such cases too and the details are in [HG94]. In the presence of certain arithmetic comparisons, we can create and push aggregation computations (Section 4.2) In particular, we saw that we can add the and functions as follows: PDRule 2 When a selection predicate is of the form B C or B C, on pushing a GP P below the selection, we ....

[Article contains additional citation context not shown here]

Venky Harinarayan and Ashish Gupta. Generalized Projections: A Powerful QueryOptimization Technique. Stanford Technical Report No. STAN-CS-TN-94-14.

....general, A; B) C) 6= A;max(B) min(C) We can replace ( by max(min) in GP s that have no other aggregate components. In the presence of other aggregate components the rules for evaluating ( are more involved. The ; operators and their algebra are explained in greater detail in [HG94]. 4.3 GP Push down Now we discuss pushing GP s down query trees and examine the interaction of GP s with the different types of nodes in the query tree. 4.3.1 Duplicate Insensitive GPs Selection Nodes: Duplicate insensitive GP s behave similarly to distinct projections if the selection ....

....( A;L=sum(B) Theta A 0 ;X=count( Since A; A 0 are keys of the left and right branches (being groupby attributes) A; A 0 is a key of the relation above the cross product. Hence we can replace sum(L X) by L X above the cross product. 2 The detailed algorithm for GP push down is in [HG94]. 4.4 Pulling Up GPs We are often interested in pulling GP s up query trees for a number of reasons. One important reason is to express queries with aggregations in a normal form (Section 5.1) The pull up rules are derivable from the push down rules we saw earlier. Selection Nodes: From query ....

V. Harinarayan and A. Gupta. Generalized Projections: A Powerful Query-Optimization Technique. Stanford Technical Report No. STANCS -TN-94-14.

Online articles have much greater impact   More about CiteSeer.IST   Add search form to your site   Submit documents   Feedback  

CiteSeer.IST - Copyright Penn State and NEC