M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proc. Int. Conf. on Very Large Data Bases (VLDB), pages 91--102, 1992.  Home/Search   Document Not in Database   Summary   Related Articles   Check

....queries to concrete evaluation algorithms. We are presenting a new XML algebra and a query optimization framework based on query normalization and query unnesting (also known as query decorrelation) There are many proposals on query optimization that are focused on unnesting nested queries [23, 18, 26, 11, 12, 9, 28]. Nested queries appear more often in XML queries than in relational queries, because most XML query languages, including XQuery, allow complex expressions at any point in a query. Current commercial database systems typically evaluate nested queries in a nested loop fashion, which is unacceptable ....

M. Muralikrishna. Improved Unnesting Algorithms for Join Aggregate SQL Queries. In Proc. Int'l. Conf. on Very Large Data Bases, pages 91-102, Vancouver, BC, Canada, August 1992.

....a set of types belonging to the set SourceSchema, denoting all the types found in the data sources, are treated as source nodes of a graph. For the rest of the types, we can derive an SQL expression by using existing view reduction algorithms, such as [33] corrected with the results of [19, 44, 45], 14, 11, 41, 48, 36] Our algorithm is applicable to graphs of activities that do not involve updates. In most cases, an update operation can be considered as the combination of insertions and deletions or as the application of the appropriate function to the relevant attributes. The results of ....

M. Muralikrishna. Improved Unnesting Algorithms for Join Aggregate SQL Queries. Proc. 18 th Intl. Conf. on Very Large Data Bases (VLDB), pp. 91-102, Vancouver, Canada (1992).

....they raise new issues. If a sequence of join operations can easily be reordered, joins and outer joins do not commute that easily and one has to find new ways of dealing with multi layered nested queries [8, 20, 22] Finally, a unifying framework for different unnesting strategies was proposed in [21]. There exist some significant differences between nested queries in the oo and the relational context. First, the result of an oo nested query is not always flat. Second, nested queries in the relational context were restricted to occur in the selection clause and, to a limited extent, in the ....

....combination is the possibility of several interesting implementations for this combination where the straightforward nested loop evaluation is only one of them. Another good reason is the reordering possibilities it offers in case of a more complex query (i.e. more levels of nesting) [8, 20, 22, 21]. Hence, we apply the following equivalence which allows to unnest nested operations: g:f(oe A 1 =A 2 (e2 ) e 1 ) A2 (e 1 1 g=f( A1=A2 ( Gamma g;A2 ;f (e 2 ) if A i A(e i ) F(e 2 ) A(e 1 ) A 1 A 2 = g 62 A(e 1 ) A(e 2 ) The superscript g = f( is the default ....

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proc. Int. Conf. on Very Large Data Bases (VLDB), pages 91--102, 1992.

....lead naturally to nested queries. Current OODB systems typically 26 evaluate nested queries in a nested loop fashion, which does not leave many opportunities for optimization. Most unnesting techniques for OODB queries are actually based on similar techniques for relational queries [Kim82, GW87, Mur92] For all but the trivial nested queries, these techniques require the use of outer joins, to prevent loss of data, and grouping, to accumulate the data and to remove the null values introduced by the outer joins. If considered in isolation, query unnesting itself does not result in performance ....

....c D k = true (e,c) G a = b a m b a true B A D m C B A E Figure 8: The Algebraic Form of Some OODB Queries which, for each department, it returns the employees of the department. The nesting inherent in this query can be avoided by using an outer join combined with grouping [Mur92] as it is shown in Figure 8.A. The nest operator, e d , groups the input by the range variable d, constructing a set of all e s that are associated with the value of d. This set becomes the value of the range variable m. That is, this nest operator reads a stream of tuples of type set(h d: ....

[Article contains additional citation context not shown here]

M. Muralikrishna. Improved Unnesting Algorithms for Join Aggregate SQL Queries. In Proc. Int'l. Conf. on Very Large Data Bases, page 91, Vancouver, BC, Canada, August 1992.

....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]

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In VLDB 1992.

....is even more powerful and is used to more elegantly unnest queries where an outerjoin followed by a unary grouping is needed. The binary grouping operator generalizes the nest join operator of [29] The optimization of relational nested queries has been studied thoroughly in the last decade [20, 19, 13, 10, 14, 24, 26, 25]. Naturally, our research has been influenced by this body of work. The classification of relational nested queries introduced in [20] proved to be useful for relational unnesting. Based on this observation, we extended this classification to the oo context. This extension is necessary due to the ....

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proc. Int. Conf. on Very Large Data Bases (VLDB), pages 91--102, 1992.

....calculus: f h D=d, E= f e e Employees, e.dno = d. dno g i d Departments g where the inner comprehension constructs a bag since the accumulator is now the bag additive union, The nesting inherent in this query can be avoided by using an outer join combined with a grouping [13], as it is shown in Figure 1.C. The Gamma ]feg d operator combines the functionality of the nested relational operator, nest, with the functionality of reduce. It groups the input by the range variable d, constructing a bag of all es that are associated with the value of a d. This bag becomes ....

M. Muralikrishna. Improved Unnesting Algorithms for Join Aggregate SQL Queries. In Proc. Int'l. Conf. on Very Large Data Bases, page 91, Vancouver, BC, Canada, August 1992.

....lead naturally to nested queries. Current OODB systems typically evaluate nested queries in a nested loop fashion, which does not leave many opportunities for optimization. Most unnesting techniques for OODB queries are actually based on similar techniques for relational queries [Kim82, GW87, Mur92] For all but the trivial nested queries, these techniques require the use of outer joins, to prevent loss of data, and grouping, to accumulate the data and to remove the null values introduced by the outer joins. If considered in isolation, query unnesting itself does not result in performance ....

....nested comprehension calculus expression: QUERY A: f h D=d, E= f e [ e Employees, e.dno = d.dno g i [ d Departments g which, for each department, it returns the employees of the department. The nesting inherent in this query can be avoided by using an outer join combined with grouping [Mur92] as it is shown in Figure 5.A. The nest operator, Gamma [ e d , groups the input by the range variable d, constructing a set of all e s that are associated with the value of d. This set becomes the value of the range variable m. That is, this nest operator reads a stream of tuples of type ....

M. Muralikrishna. Improved Unnesting Algorithms for Join Aggregate SQL Queries. In Proc. Int'l. Conf. on Very Large Data Bases, page 91, Vancouver, BC, Canada, August 1992.

....in network databases and does not leave many opportunities for optimization. In addition, OODB queries can be arbitrarily nested. If a nested query is evaluated as is, its execution would resemble a nested loops join. Some forms of nesting can be avoided by using outer joins combined with grouping [13, 6]. In most OODB algebras, where grouping is an explicitly supported operator and can appear in any place in an algebraic form, any form of query nesting can be unnested by promoting the inner queries one level up in the query nesting hierarchy [3, 4] This results to a large flat join, which is the ....

M. Muralikrishna. Improved Unnesting Algorithms for Join Aggregate SQL Queries. In Proc. Int'l. Conf. on Very Large Data Bases, page 91, Vancouver, BC, Canada, August 1992.

....substantial gain in efficiency due to unnesting. In order to eliminate some bugs which where subsequently detected, outer joins were used [14, 7, 9, 17] Reordering of outer joins became an important topic [7, 17, 19] Last, a unifying framework for different unnesting strategies was proposed in [18]. There exist some significant differences between nested queries in the oo and the relational context. First, the result of an oo nested query is not always flat. Second, nested queries in the relational context were restricted to occur in the selection clause only. In the oo context, nested ....

....combination is the possibility of several interesting implementations for this combination where the straightforward nested loop evaluation is only one of them. Another good reason is the reordering possibilities it offers in case of a more complex query (i.e. more levels of nesting) [7, 17, 19, 18]. Hence, we apply the following equivalence which allows to unnest nested operations: g:f(oe A 1 =A 2 (e 2 ) e 1 ) A 2 (e 1 1 g=f( A 1 =A2 ( Gamma g;A2 ;f (e 2 ) 1) if A i A(e i ) F(e 2 ) A(e 1 ) A 1 A 2 = g 62 A 1 [ A 2 The superscript g = f( is the default ....

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proc. Int. Conf. on Very Large Data Bases (VLDB), pages 91--102, 1992.

....mapping between tables and classes. We show that the translation of SQL queries benefits considerably from these extra semantics. ffl Since Meng et al. focus on the operational aspects of query translation, they do not discuss the translation of nested queries, as there exist unnesting algorithms [12,14] that transform nested queries into an equivalent collection of simple queries. We show that this approach would be inadequate for our purposes, as it would obscure the meaning of the original query. Existing work on schema translation is of course still relevant in our approach, as we assume the ....

....Ph Name : string i) collect h Name = x.name i for x in self iff not exists y in x.dependents j y.sex= F Figure 6: Two translations of the example query Translating nested queries In [13] Meng et al. do not consider the translation of nested queries explicitly. Existing unnesting algorithms [8,12,14] are relied upon to first flatten unnested queries; subsequently a general translation procedure for unnested queries is applied. In our context, such a procedure would be unsatisfactory. These algorithms in general result in collections of simple queries, with temporary relations holding ....

M. Muralikrishna, "Improved unnesting algorithms for join aggregate SQL queries, " in Proceedings of Eighteenth International Conference on Very Large Data Bases, Vancouver, Canada, August 23--27, 1992 , L-Y. Yuan, ed., Morgan Kaufmann Publishers, San Mateo, CA, 1992, 91--102.

....other hand, if the query is initially the Project expression it would be useful to generate the Join query, especially if there are efficient techniques for dealing with Join operations. Processing of nested query expressions in SQL addresses situations where queries are nested in Where predicates [51, 38, 83, 109]. Proposals for handling nested queries in object oriented SQL like languages address nesting in From and Where clauses [31, 115] The problem discussed here is equivalent to a query expression nested in an SQL Select clause. This problem is addressed by a new optimization strategy discussed in ....

....using Outerjoin (and occasionally Join) operations. If translated to SQL like queries, the nested queries would appear in the Select clause of the query. Thus, this region complements recently proposed techniques for dealing with SQL like queries with nested queries in From and Where clauses [31, 38, 109, 115]. The applicability predicate for this region states that it looks for queries with Image and Select operations, Project and Select operations, or Select with Exist operations. In particular, the region can transform Selects nested within Images or Projects, and Selects with an existence ....

M. Muralikrishna. Improved Unnesting Algorithms for Join Aggregate SQL Queries. In Proceedings of the 18th VLDB Conference, pages 91--102, 1992.

....With the increasing emphasis on decision support systems, the problem of optimizing queries with aggregates have assumed increasing importance. This article has pointed to some of the recent devel 1 Note that for correctness, such transformations require outerjoin in the general case [GW87, Mur92] opments in this area. However, the evolving querying models for decision support systems (e.g. OLAP interfaces) will spur further development in this area. To Probe Further Invariant grouping for a single block query was studied independently by us in [CS94] and in [YL94] Our paper also ....

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proceedings of the 18th International VLDB Conference, Vancouver, Canada, August 1992.

....move around improves the ability to determine the optimal join order. The magic set transformation can be applied after predicate move around in order to move join predicates in the direction of the join order. There has been a lot of work on optimizing subqueries and eliminating correlations [Kim82, GW87, Day87, Mur92]. Our technique complements well with that work by providing a new powerful means of pushing predicates after correlations are removed. A predicate is said to be expensive if the cost of applying the predicate is high. Placement of expensive predicates has been studied by [HS93, Hel94] The ....

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In VLDB 1992, pages 91--102.

....in network databases and does not leave many opportunities for optimization. In addition, OODB queries can be arbitrarily nested. If a nested query is evaluated as is, its execution would resemble a nested loops join. Some forms of nesting can be avoided by using outer joins combined with grouping [12]. In most OODB algebras, where grouping is an explicitly supported operator and can appear in any place in an algebraic form, any form of query nesting can be unnested by promoting the inner queries one level up in the query nesting hierarchy [3, 4] This results to a large flat join, which is the ....

M. Muralikrishna. Improved Unnesting Algorithms for Join Aggregate SQL Queries. In Proc. Int'l. Conf. on Very Large Data Bases, page 91, Vancouver, BC, Canada, August 1992.

....In such cases, the inner query block needs to be evaluated only once. However, when there is indeed a variable from the outer block, we say that the query blocks are correlated. For example, in the query above, Emp.Emp# acts as the correlated variable. Kim [35] and subsequently others [16,13,44] have identified techniques to unnest a correlated nested SQL query and flatten it to a single query. For example, the above nested query reduces to: SELECT E.Name FROM Emp E, Dept D WHERE E.Dept# = D.Dept# AND D.Loc = Denver AND E.Emp# = D.Mgr Dayal [13] was the first to offer an algebraic ....

....Emp. Where Join(Emp,Dept) is on the predicate Emp.Dept# = Dept. Dept# . The second argument of the Project operator 3 indicates that all columns of the relation Emp must be retained. The problem is more complex when aggregates are present in the nested subquery, as in the example below from [44] since merging query blocks now requires pulling up the aggregation without violating the semantics of the nested query: SELECT Dept.name FROM Dept WHERE Dept.num of machines (SELECT COUNT(Emp. FROM Emp WHERE Dept.name= Emp.Dept name) It is especially tricky to preserve duplicates and ....

[Article contains additional citation context not shown here]

Muralikrishna, M. Improved Unnesting Algorithms for Join Aggregate SQL Queries. In Proc. of VLDB, Vancouver, 1992.

....2 data structures. Optimization of TM SFW expressions therefore has much in common with optimization of the SFW expressions of SQL and HDBL. Optimization of SQL queries has received quite some attention the last decade. An important problem in this area is the optimization of nested SQL queries [7, 5, 4, 8, 9]. SQL offers possibilities to formulate nested queries: SFW query blocks containing other SFW blocks in the WHERE clause. In [7] it was pointed out that it is advantageous to replace nested SQL queries by flat, or join queries. Flat SQL queries are SFW blocks not containing subqueries in the ....

....of joins in case the COUNT function occurs between query blocks. The right outerjoin operator preserves dangling tuples of the left join operand: unmatched left operand tuples are extended with NULL values in the right operand attribute positions. As another solution, it has been proposed in [9] to modify (1) because in some cases (1) is more efficient than (2) It is proposed to have two types of join predicates in the second query of (1) a regular join predicate and an additional, so called antijoin predicate, to be applied to the dangling tuples. In the example given above the ....

Muralikrishna, M.: Improved Unnesting Algorithms for Join Aggregate SQL Queries. Proceedings VLDB. Vancouver, 1992.

....for join ordering. For example, the alternative execution suggested in Example 1. 1 cannot be obtained by transformations in [YL93] Prior work on group by has addressed the problem of pipelining group by and aggregation with join [D87, Kl82b] as well use of group by to flatten nested SQL queries [K82, D87, G87, M92]. But, these problems are orthogonal to the problem of optimizing queries containing group by that we are addressing in this paper. 1.5 Outline Section 2 discusses the preliminary concepts and assumptions. In Section 3, we define the proposed transformations. Section 4 is devoted to the ....

Muralikrishna M. "Improved Unnesting Algorithms for Join Aggregate SQL Queries," in Proceedings of the 18th VLDB , 1992.

....problem in its own right, but it also directly bears upon the problem of optimizing queries with nested subqueries. This follows from the past work in flattening to handle an important class of queries with nested subqueries (join aggregate queries) pioneered by Kim [Kim82] cf. GW87, Day87, Mur92] The result of Kim s transformation on a query with nested subqueries is a query that is a join 3 of base tables and one or more aggregate views. Thus, using Kim s transformation, the result of optimizing queries containing aggregate views can be used for optimizing an important class of ....

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proc. of the 18th VLDB Conference, Vancouver, Canada, August 1992.

....allow complex expressions at any point in a query. Current OODB systems typically evaluate nested queries in a nested loop fashion, which does not leave many opportunities for optimization. Most unnesting techniques for OODB queries are actually based on similar techniques for relational queries [15, 14, 17]. For all but the trivial nested queries, these techniques require the use of outer joins, to prevent loss of data, and grouping, to accumulate the data and to remove the null values introduced by the outer joins. If considered in isolation, query unnesting itself does not result in performance ....

....) from d in Departments This query has a straightforward translation in the monoid comprehension calculus: QUERY B: f h D=d, E= f e e Employees, e.dno = d. dno g i d Departments g The nesting inherent in this query can be avoided by using an outer join combined with grouping [17], as it is shown in Figure 1.B. The nest operator, Gamma [ e d , combines the functionality of the nested relational operator, nest, with the functionality of reduce. It groups the input by the range variable d, constructing a set of all e s that are associated with the value of d. This set ....

M. Muralikrishna. Improved Unnesting Algorithms for Join Aggregate SQL Queries. In Proc. Int'l. Conf. on Very Large Data Bases, page 91, Vancouver, BC, Canada, August 1992.

....several bugs in this approach have been detected and corrected [9, 11, 13] The main point is to use an outer join instead of a regular join. Nevertheless, the main idea join, group, aggregate still remains the same and is even today the only known improvement over nested loops [7, 16, 17]. Let us call this approach A G J. Lately, it was proposed to exchange grouping and aggregate operations with joins [19, 18, 20, 5, 6] This work is orthogonal to our s in that our method can be used to implement the grouping and aggregate operations used there. Consider the performance of this ....

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proc. Int. Conf. on Very Large Data Bases (VLDB), pages 91--102, 1992.

....They do not give any rules to infer aggregation predicates between different 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 ....

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proceedings of the Eighteenth International Conference on Very Large Databases (VLDB), pages 91--102, Vancouver, Canada, August 23-27 1992.

....the basis of its cost model. In this section, we identify three importantquery transformations:detection of unnecessary duplicate elimination, conversion of a subquery to a join, and conversion of an intersection to a subquery. Other researchers have described these query transformations elsewhere [6, 11, 17, 19] but with relatively little formalism. Later, in Section 6, we show the applicability of these transformations in nonrelational environments. 5.1 Unnecessary duplicate elimination We believe that many queries contain unnecessary Distinct clauses, for two reasons. First, case tools often ....

....is a form of semantic query optimization [12] Kim [11] originally suggested rewriting correlated, nested queries as joins to avoid nested loop execution strategies. Subsequently, several researchers corrected and extended Kim s work, particularly in the aspects of grouping and aggregation [1, 6, 7, 10, 17]. None of this work explicitly addresses applicability of these techniques in other database environments; as our examples show, nested loops remain an attractive execution strategy, under certain conditions, with a variety of database architectures. Much of the earlier work in semantic ....

M. Muralikrishna. Improved unnesting algorithms for join aggregate sql queries. In Proc. vldb 18, pages 91-- 102, Vancouver, bc, 1992.

No context found.

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proc. Int. Conf. on Very Large Data Bases (VLDB), pages 91--102, 1992.

No context found.

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proc. Int. Conf. on Very Large Data Bases (VLDB), pages 91--102, 1992.

No context found.

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proc. Int. Conf. on Very Large Data Bases (VLDB), pages 91--102, 1992.

No context found.

M. Muralikrishna. Improved Unnesting Algorithms for Join Aggregate SQL Queries. In 18th International Conference on Very Large Data Bases, pages 91--102, 1992.

No context found.

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proc. Int. Conf. on Very Large Data Bases (VLDB), pages 91--102, 1992.

No context found.

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proc. Int. Conf. on Very Large Data Bases (VLDB), pages 91--102, 1992.

No context found.

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proc. Int. Conf. on Very Large Data Bases (VLDB), pages 91--102, 1992.

No context found.

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proc. Int. Conf. on Very Large Data Bases (VLDB), pages 91--102, 1992.

No context found.

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proc. Int. Conf. on Very Large Data Bases (VLDB), pages 91--102, 1992.

No context found.

M. Muralikrishna. Improved Unnesting Algorithms for Join Aggregate SQL Queries. In Proc. of 18 th VLDB, 1992.

No context found.

M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In VLDB 1992.

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