Inderpal S. Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is relevant. In Proc. ACM SIGMOD Intl. Conf. on Management of Data, pages 247--258. 1990.  Home/Search   Document Not in Database   Summary   Related Articles   Check

....ffl Conflict resolution and query evaluation strategies are proposed to ensure the proper evaluation of multiple, semantically equivalent queries derived from the initial F logic expression. Association of recursive rules [12] with a query is a topic for future research. Magic set theory [9] may be also employed in our query reformulation process. Acknowledgement This research is supported in part by an ARPA grant, administered by the Office of Naval Research under grant number N0014 92 J 4038. ....

Inderpal S. Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is relevant. In Proc. ACM SIGMOD Intl. Conf. on Management of Data, pages 247--258. 1990.

....caching and common subexpressions; those approaches can coexist profitably with the techniques presented here. Correlated SQL subqueries can be considered as a form of expensive method [HS93] It has been demonstrated that the magic sets rewriting can be used to speed up such subqueries [MFPR90, SPL96] even in non recursive SQL. This magic decorrelation avoids redundant computation by first computing the (duplicate free) set of all input values to a correlated subquery, then feeding all the distinct input values into the subquery at once, and finally joining the result of all the ....

.... as the techniques presented in this paper: the cost of forming the duplicate free input set is equivalent to the cost of building a method cache, and on top of this cost magic rewriting requires an additional join and possibly also an additional materialization of the supplementary input [MFPR90] For expensive subqueries, there are tradeoffs between magic and caching. Seshadri et al. propose new techniques for cost based optimization of magic [SHP 96] which can be extended to the problem of choosing whether to use magic or caching for subqueries in a query plan. Discussion of using ....

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is Relevant. In Proc. ACMSIGMOD International Conference on Management of Data, pages 247--258, Atlantic City, May 1990.

....present four COKO transformations that depend on prior normalization by SNF: predicate pushdown (Section 4.5.1) join reordering (Section 4.5.2) the transformation of the Conflict of Interests query (COI 2 k ) from Section 3.5.2 (Section 4.5. 3) and the Magic Sets transformation presented in [MFPR90] Section 4.5.4) 75 TRANSFORMATION Pushdown USES push: join ( p Phi 1 ) q Phi 2 ) r; f) A; B] join (r; f) iterate (p; id) A; iterate (q; id) B] SNF, simplify: iterate (K p (true) id) A = A BEGIN GIVEN join (p; F) O DO SNF (p) push; GIVEN O [A; B] ....

....3.5.2 that rewrites a predicate to the form, p Phi 2 can be replaced by a call to SNF. However, SimpLits would have to first be extended to handle quantification predicates (by adding rules 6a and 6b for example) 4.5. 4 Magic Sets The idea behind the Magic Sets transformation presented in [MFPR90] is to restrict inputs to joins by filtering those that cannot possibly satisfy the join predicate. Therefore, this transformation is in the spirit of predicate pushdown, but passing filter predicates sideways from one join input to another, rather than down from the join predicate. As with ....

[Article contains additional citation context not shown here]

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is relevant. In Proc. ACM SIGMOD Int'l Conference on Management of Data, pages 247--258, 1990.

....be obtained by examining relationships than by examining individual pieces of data. Thus, joins are a very important operation. Unfortunately, they can also be very expensive to compute. As a result, a large amount of research on relational databases has been aimed at computing joins efficiently [SELI79, JARK84, DEWI84, GERB86, SCHN89, MUMI90, DEWI92b]. For an OODBS to satisfy customer performance requirements, it must effectively optimize joins. This is more complex for the database programming language (DBPL) of an OODBS than for a relational system, because there are a number of different ways to express a join in such systems. In this ....

....query. These subqueries can be simplified further by other transformations. DAYA87, GANS87, MURA89] corrected errors in Kim s technique by replacing joins with outerjoins. DAYA87, MURA89] developed pipelining techniques that remove some of the temporary relations introduced by Kim s technique. [MUMI90] uses this prior work to convert queries with nested query blocks into a series of queries and view definitions. It then applies the magic sets transformation to reduce the amount of work required to compute the desired result by eliminating useless work performed in some cases by the earlier ....

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, Raghu Ramakrishnan. Magic is Relevant. Proc. 1990 SIGMOD, June 1990.

....ours seems to perform reasonably well for our purposes. One can do per function caching instead, as proposed in [Jhi88] and [HS93a] Function or predicate caches can be limited in size, using any of a variety of replacement schemes. Queries can be rewritten with Magic Sets techniques [BMSU86, MFPR90] to avoid the issue of caching entirely, at the expense of extra joins and common subexpressions. Such alternatives do not form a focus of this paper, as this space of possible implementations is large and orthogonal to the space of optimization techniques explored here. Although this is ....

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is Relevant. In Proc. ACMSIGMOD International Conference on Management of Data, pages 247--258, Atlantic City, May 1990.

.... [PDR91, DMP93] implement the supplementary magic sets transformation [BR91] and Starburst [MP94] implements magic conditions [MFPR90a] Performance experiments have shown the magic sets transformation to be a good optimization technique, both for recursive and nonrecursive queries [BR88, MFPR90b] However, magic sets transformation has not found its way into commercial databases. A prime reason is the problem with determining join orders needed for the adornment phase of the magic sets transformation. A second problem is the proliferation of adorned variants of each predicate when trying ....

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is relevant. In Proceedings of ACM SIGMOD 1990 International Conference on Management of Data, pages 247--258, Atlantic City, NJ, May 23-25 1990.

.... the result of optimizing queries containing aggregate views can be used for optimizing an important class of queries with correlated nested subqueries [CS96] Unfortunately, the techniques for optimizing queries with aggregate views have been limited to propagating predicates across query blocks [MFPR90, LMS94] to reduce the cost of optimizing each query block. However, subsequent to this preprocessing, each query block is optimized locally using the optimization algorithm for SPJ queries. What s missing is the ability to enumerate plans that 3 In some cases, such transformations may introduce ....

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is relevant. In Proc. of the ACM SIGMOD, Atlantic City, May 1990.

....to the work on persistent caching and common subexpressions, since those approaches can coexist profitably with our work. Correlated SQL subqueries can considered as a form of expensive method [HS93] It has been deomnstrated that the magic sets rewriting can be used to speed up such subqueries [MFPR90, SPL96] even in non recursive SQL. This magic decorrelation avoids redundant computation by first computing the (duplicate free) set of all input values to a correlated subquery, then feeding all the distinct input values into the subquery at once, and finally joining the result of all the ....

.... as the techniques presented in this paper: the cost of forming the duplicate free input set is equivalent to the cost of building a method cache, and on top of this cost magic rewriting requires an additional join and possibly also an additional materialization of the supplementary input [MFPR90] For expensive subqueries, there are tradeoffs between magic and caching. Seshadri et al. propose new techniques for cost based optimization of magic [SHR 95] which can be extended to the problem of choosing whether to use magic or caching for subqueries in a query plan. 2 Background 2.1 ....

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is Relevant. In Proc. ACM-SIGMOD International Conference on Management of Data, pages 247--258, Atlantic City, May 1990.

....the performance of the Predicate Migration Algorithm against more naive predicate pullup heuristics. It would be interesting to attempt to extend this work to handle queries with common subexpressions and recursion. The Magic Sets optimization technique for recursive and non recursive queries [MFPR90] actually generates predicates in a query plan and pushes them down. It is not clear when this generation is cost effective, and our model here may be useful for making that decision. Finally, our cost analyses for user defined functions could be dramatically improved by techniques to more ....

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is Relevant. In Proc. ACM-SIGMOD International Conference on Management of Data, pages 247--258, Atlantic City, May 1990.

....useful. Second, a rule system is an excellent platform for extensibility, one of the key goals of Starburst s design. This extensibility has allowed us to write and test dozens of query transformations over the past two years, including those presented in this paper, magic sets transformations [MFPR90a, MPR90, MFPR90b] and numerous others. As we shall see, the rules presented in this paper demonstrate that Query Rewrite can often speed up query execution by orders of magnitude, suggesting that query transformation schemes form a ripe area of research. Our extensible Query Rewrite system is ....

....Ganski and Wong [GW87] and Dayal [Day87] did additional work on eliminating nested subqueries. These papers recognize the importance of merging of subqueries. Kim82, GW87] also deal with subqueries containing aggregation. We have reported our set of rules that deal with such subqueries in [MFPR90a, MPR90, MFPR90b] Ganski s paper illustrates the complexity of query rewrite, since it has to emend some previous transformations which were incorrect. This complexity supports our approach of decomposing transformations into an extensible set of distinct rules, such that each rule can be shown ....

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is Relevant. In Proc. SIGMOD 90 [Pro90], pages 247--258.

....that rewrites a given program and query so that a bottom up evaluation on the rewritten program is more efficient than on the original program, due to a reduction in the number of tuples computed and looked up in the database. Magic sets has been recognized to be of extreme importance ( BR88, MFPR90] and many new database systems (Starburst [HCL 90] for example) use the magicsets transformation as a basic optimization. However, special classes of recursive queries have been identified for which magic sets may not be the best rewrite strategy. The right linear queries, defined by ....

....bb (X;Y ) par(X; Z) anc bf (Z;Y ) 2 2 Overbound Queries A commonly used heuristic in query optimization is that all available bindings should be used to restrict computation. Such a strategy is called full sips. Full sips are widely used in systems (in NAIL [MNS 87] and Starburst [MFPR90] for example) RL and CRL are based on full sips, and magic sets is usually done with respect to a full sips. However, we will see in Example 2.1 that sometimes all the bindings are not useful in restricting computation, and attempts to use the extra bindings can make the computation ....

[Article contains additional citation context not shown here]

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is relevant. In Proceedings of ACM SIGMOD 1990 International Conference on Management of Data, pages 247--258, Atlantic City, NJ, May 23-25 1990.

....by a recursive SQL or Datalog program may be infinite. EXAMPLE 1.1 (Path Query) We are given an edge(X; Y ) relation, and need to find the number of paths between every pair of nodes X and Y . A path(X; Y ) table can be defined in SQL extended with recursion (we use the syntax of Starburst [MFPR90]) as follows: N ) CREATE VIEW path(X;Y ) AS ( SELECT X;Y FROM edge) UNION ALL (SELECT e:X, p:Y FROM edge e, path p WHERE e:Y = p:X) Q) SELECT X, Y , COUNT ( FROM path GROUPBY X;Y . The path view in SQL is equivalent to the following definition in Datalog if we interpret path as a ....

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is relevant. In Proceedings of ACM SIGMOD 1990 International Conference on Management of Data, pages 247--258, Atlantic City, NJ, May 23-25 1990.

....( BR87, Ull89] is a general purpose query optimization technique initially proposed for recursive queries in deductive databases. It has been shown that the magic sets transformation (MST) can be extended to optimize relational SQL queries where SQL has been extended to permit recursion [MFPR90, MPR90, Mum91]. Performance experiments have demonstrated that MST is an invaluable optimization for nonrecursive queries [MFPR90, Mum91] It is thus imperative that MST be used to optimize queries in a nonrecursive system. However, there is one problem that must be solved before MST can be used in a ....

....databases. It has been shown that the magic sets transformation (MST) can be extended to optimize relational SQL queries where SQL has been extended to permit recursion [MFPR90, MPR90, Mum91] Performance experiments have demonstrated that MST is an invaluable optimization for nonrecursive queries [MFPR90, Mum91]. It is thus imperative that MST be used to optimize queries in a nonrecursive system. However, there is one problem that must be solved before MST can be used in a nonrecursive system. MST has the undesirable property that it can transform a nonrecursive query into a recursive query, as in ....

[Article contains additional citation context not shown here]

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is relevant. In Proceedings of ACM SIGMOD 1990 International Conference on Management of Data, pages 247--258, Atlantic City, NJ, May 23-25 1990.

No context found.

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is Relevant. In Proceedings of ACM SIGMOD 1990 International Conference on Management of Data, Atlantic City, NJ, pages 247--258, May 23-25 1990.

....queries using equijoin predicates. We extend the magic sets transformation to use predicates other than equality (X 10, for example) This Extended Magic Set technique has practical utility in real relational databases, not only for recursive queries, but for non recursive queries as well; in ([MFPR90]) we use the results in this paper and those in [MPR90] to define a magic set transformation for relational databases supporting SQL and its extensions, going on to describe an implementation of magic in Starburst ( HFLP89] We also give preliminary performance measurements. In extending ....

....we have an adorned version that corresponds to all uses of that predicate with a binding pattern that is described by the adornment; different adorned versions are treated as different predicates (and possibly solved differently) For example, 5 Extensional DataBase, or base predicates. 6 In [MFPR90] we introduce a one phase variant, and compare it with the two phase algorithm. p bf and p fb , are treated as (names of) distinct predicates. An adornment for an n ary predicate is defined to be a string of b s and f s. Argument positions that are treated as free (have no predicate on ....

[Article contains additional citation context not shown here]

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is Relevant. In Proceedings of ACM SIGMOD 1990 International Conference on Management of Data, Atlantic City, NJ, pages 247--258, May 23-25 1990.

....( BR87, Ull89] is a general purpose query optimization technique initially proposed for recursive queries in deductive databases. It has been shown that the magic sets transformation (MST) can be extended to optimize relational SQL queries where SQL has been extended to permit recursion [MFPR90, MPR90, Mum91]. Performance experiments have demonstrated that MST is an invaluable optimization for nonrecursive queries [MFPR90, Mum91] It is thus imperative that MST be used to optimize queries in a nonrecursive system. However, there is one problem that must be solved before MST can be used in a ....

....databases. It has been shown that the magic sets transformation (MST) can be extended to optimize relational SQL queries where SQL has been extended to permit recursion [MFPR90, MPR90, Mum91] Performance experiments have demonstrated that MST is an invaluable optimization for nonrecursive queries [MFPR90, Mum91]. It is thus imperative that MST be used to optimize queries in a nonrecursive system. However, there is one problem that must be solved before MST can be used in a nonrecursive system. MST has the undesirable property that it can transform a nonrecursive query into a recursive query, as in ....

[Article contains additional citation context not shown here]

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is relevant. In Proceedings of ACM SIGMOD 1990 International Conference on Management of Data, pages 247--258, Atlantic City, NJ, May 23-25 1990.

....the inference process would have used aggregation predicates as intermediate predicates to derive ordinary predicates that can be used in the optimized query to restrict computation. To describe the use of aggregation reasoning in optimization, we will represent an SQL query using a query graph [MFPR90, PHH92, MP94, LMS94]. A query graph is a pictorial representation of the query. For example, the marketing query Q from Section 2 is represented by the query graph of Figure 1. The graph has ptCustomers SELECT GROUPBY SELECT QUERY GROUPBY wellcalled pto calls customers Fig. 1. The query graph for the marketing query ....

....types of aggregations or between views and relations. There has been a lot of work on optimizing queries with aggregation in correlated subqueries by way of decorrelation (converting the subqueries into views) Kim82, GW87, Day87, Mur92] with perhaps a magic sets transformation to follow [MFPR90, MP94]. A different type of optimization involving aggregation was described by Chaudhuri and Shim [CS94] and Yan and Larson [YL95] The main observation in that work is that often it is possible to perform a grouping operation before a join or selection operation in the same query block. Doing so may ....

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is relevant. In Proceedings of ACM SIGMOD 1990 International Conference on Management of Data, pages 247--258, Atlantic City, NJ, May 23-25 1990.

No context found.

Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is relevant. In Proceedings of the ACM SIGMOD International Conference on Management of Data, Atlantic City, NJ, May 1990.

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