1 / 31

Aliaksei A. Holubeu

Optimizing an SQL-like Nested Query. Advances in Database Query Processing. Aliaksei A. Holubeu. Universit ä t Konstanz, 2005. Overview :. Introduction - Classification of nested queries - Algorithms to transform nested to non-nested queries - Potential performance improvements

cricket
Download Presentation

Aliaksei A. Holubeu

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Optimizing an SQL-like Nested Query Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  2. Overview: • Introduction • - Classification of nested queries • - Algorithms to transform nested to non-nested queries • - Potential performance improvements • - Strategy to process SQL-like nested queries • of arbitrary complexity Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  3. Introduction: The illustrative examples used in this presentation are based on the following databaseof suppliers, parts, projects, and shipments: Throughout this presentation Ridenotes a relation in the database, Ckthe k th columnof a relation, Bthe size in pages of available main-memory buffer The fundamental structure of an SQL-like query is syntactically represented bya query blockwhich consists of SELECT, FROM, and WHERE clauses. Ex.:1 SELECTMAX(PN0) FROMPART WHEREPRICE > ‘25’; SELECT<output coulumns> FROM <indicates the relation> WHERE < predicate, [Ri.Ck op X]> whe X – const. or number of const. op – scalar comparison operator (<, >, <=, etc.)or set membership operator (IS IN, IS NOT IN) Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  4. Nesting of the Query Blocks: predicates Extension of the simple predicates of the form [Ri.Ck op X], for n-relation queries: Nested predicate. [Ri.Ck op Q], where Q - SQL-like query block op - may be a scalar or setmembership operator. if [Q op Ri.Ck]. set membership operator is then replaced by the set containment operator(CONTAINS, DOES NOTCONTAIN). Join predicate.[Ri.Ck op Rj.Ch], where Ri ¬= Rj op - a scalar comparisonoperator, here (=) operator. Division predicate.[Qi op Qi]. where op - scalar comparison operator, set comparison operator , set membershipand containment operator. Qi (or Qj) may be a constant ora list of constants, provided - Qi (or Qi) has in its WHERE clause a joinpredicate which references the relation of the outer query block. Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  5. Nesting of the Query Blocks: predicates Onlythe nested predicate and the division predicate give rise to the nesting of queryblocks. A nested predicate may cause one of four basic types of nesting, and a division predicate yields a fifth basicnesting. Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  6. A (Ck) Ri Rj A type-A nested query (predicate) of depth 1 is graphically represented as follows. Nesting of the Query Blocks: type – A nesting • The inner query block Q doesnot contain a • join predicate that references the relation • of the outer query block • the SELECT clause of Q indicates an • aggregate function associated with the • column name. Ex.2 SELECT SNO FROMSHIPMENT WHEREPNO = (SELECT MAX(PN0) FROMPART WHERE PRICE > ‘25’) ; • There is only one way to process a type-A nested query on a single processor: • the inner block has to be evaluated first. • the nested predicate of the outer blockthen becomes a simple predicate, • since Q can be replaced by a constant. • 3. theouter block then is no longer nestedand can be processed completely. Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  7. A type-N nested query of depth n(n >= 1) is defined as a nested query whosequery graph consists of only straight arcs labeled ‘N’. N (Ck) Rj Ri The query graph for a type-N nested query of depth 1: N (Cn) N (Cm) Ri Rj Rk If op is the setnon-inclusion operator (IS NOT IN) requires a different treatment. Nx (Ck) Ri Rj Nesting of the Query Blocks: type – N nesting • Q does not contain a join predicate that • references the relation of the outer query block • the column name in theSELECT clause of Q • does not have an aggregate function • associated with it. Ex.3 SELECT SNO FROMSHIPMENT WHEREPNO IS IN (SELECT PNO FROMPART WHERE PRICE > ‘25’) ; Note!!! Note that Ex. 3 shows other formulations of the canonical query: SELECT SNO FROMSHIPMENT, PART WHERESHIPMENT.PNO = PART.PNO ANDPRICE > ‘25’; This observation has some interesting implications,as can be seen later. • The System R approach to processing the above query is: • to process theinner query block Q in order to replace the type-N nested predicate, • PNO IS INQ, with a simple predicate PNO IS IN X, where X is the result of evaluating Q, • 2. then to completely process the resulting nonnested query. Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  8. N (Ck) Ri Rj A type-J nested query of depth n(n >= 1) is defined as a nested query whose query graph consists of at least one circular arc and no straight arc labeled ‘A’. The query graph for a type-J nested query of depth 1: Ri.Cn = Rj.Cm N (Cn) N (Cm) Ri Rj Rk Ri Rj Nx (Ck) The query graph for a type-J nested query of depth 1 involving the set non-inclusion operator. Ri.Cn = Rk.Cm Ri.Cn = Rj.Cm Nesting of the Query Blocks: type – J nesting • the WHERE clause of Q contains a join • predicate that references the relation of the • outer query block • the column name in the SELECT clause of Q • is not associated with anaggregate function, Ex.4 SELECT SNO FROMSHIPMENT WHEREPNO IS IN (SELECT PNO FROM PROJECT WHERE SHIPMENT.SNO = PROJECT.JNO ANDJLOC = ‘NEW YORK’) ; Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  9. A type-JA nested query of depth n(n >= 1) is defined as a nested query whosequery graph exhibits at least one circular arc and at least one straight arc labeled‘A’, A (Ck) Ri Rj N (Cn) A (Cm) Ri Rj Rk The query graph for a type-JA nested query of depth 1: Ri.Cn = Rj.Cm Ri.Cn = Rk.Cm Nesting of the Query Blocks: type – JA nesting • the WHERE clause of Q contains a join • predicate that references the relation of the • outer query block • an aggregate function is associated with the • column name in the SELECT clauseof Q. Ex.5 SELECT SNO FROMSHIPMENT WHEREPNO = (SELECT MAX( PNO) FROM PROJECT WHERE PROJECT.JNO = SHIPMENT.JNO ANDJLOC = ‘NEW YORK’); Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  10. Ri.Cn = Rj.Cm The query graph for a type-D nested query of depth 1: Rj Ri Rk A (Ck) Nesting of the Query Blocks: type – D nesting A join predicate and a division predicate together give rise to a type-D nesting. • the join predicate in either Qi or Qj (or both) references the relation of the outer block. Ex.6 SELECT SNAME FROMSUPPLIER WHERE (SELECT PNO FROM SHIPMENT WHERE SHIPMENT.SNO = SUPPLIER.SNO) CONTAINS (SELECT PNO FROM PART WHERE COLOR = ‘RED’ ANDPRICE > ‘25’) ; Type-D nested query expresses the relational division operation. Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  11. Processing a Type-N or Type-J Nested Query: 1 some query may be expressed in its canonicalform or type-N nested form. Proof: By definition, the inner block of Q2 can be evaluated independently ofthe outer block and the result of evaluating it is X, a list of values in the C,column of Rj. Qz is then reduced to: SELECT Ri.Ck FROMRi WHERERi.Ch ISIN X; The predicate Ri. Ch IS IN X is satisfied only if X contains a constant x such that Ri.Ch = X. That is, it can be satisfied only for those tuples of Ri and Rj which have common values in the Ch and Cm, columns, respectively. The join predicate Ri. Ch = Rj. Cm specifies exactly this condition. LetQ1be SELECT Ri.Ck FROM Ri, Rj WHERE Ri.Ch = Rj.Cm And let Q2 be SELECT Ri.Ck FROM R, WHERE Ri.Ch ISIN (SELECT Rj.Cm FROM Rj); LEMMA 1. Q1 and Q2 are equivalent; that is, they yield the same result. establishes the equivalence of the canonical and type-N nested form of a two-relation query in which the op is not the set non-inclusion operator,IS NOT IN. Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  12. Processing a Type-N or Type-J Nested Query: 2 It is important to recognize that Lemma 1 only establishes that the type-N nested form of a query in which the op of the nested predicate is the set inclusion operator can be transformed to its canonical form. Lemma 1 suggests Algorithm NEST-N-J for transforming a type-N nested query of depth n - 1 to its canonical form. Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  13. = Rj.Cm SELECTRi.Ck FROMRi, Rj WHERERi.Ch = Rj.Cm AND Ri.Cn=Rj.Cp); AND Processing a Type-N or Type-J Nested Query: 3 Algorithm NEST-N-J: 1 Combine the FROM clauses of all query blocks into one FROM clause. 2 AND the WHERE clauses of all query blocks into one WHERE clause. 3 Replace [Ri.Ch op (SELECT Rj.Cm] by a join predicate [Ri.Ch, new-op Rj.Cm], and AND it to the combined WHERE clause obtained on step 2. Note that if op is IS IN, the corresponding new-op is ‘=‘; otherwise - the same as op. 4 Retain the SELECT clause of the outermost query block. SELECTRi.Ck FROMRi WHERERi.Ch IS IN (SELECT Rj.Cm FROM Rj WHERE Ri.Cn=Rj.CP); Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  14. Processing a Type-N or Type-J Nested Query: 4 SELECT SNAME FROM SUPPLIER WHERE SNO IS NOT IN (SELECT SNO FROM SHIPMENT WHERE PNO = ‘Pl’) ; alternative interpretation of thequery is a type-D nested query. SELECT SNAME FROMSUPPLIER WHERE (SELECT PNO FROM SHIPMENT WHERE SHIPMENT.SNO = SUPPLIER.SNO) DOES NOT CONTAIN Pl; Pseudo-canonical form: SELECT SNAME FROMSUPPLIER, X WHERE¬(SUPPLIER.SNO = X.SNO); Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  15. Processing a Type-N or Type-J Nested Query: 5 The set noninclusion operator in a type-N or type-J nested query requires an extension to Algorithm NEST-N-J. Now [Ri.Ck IS NOT IN (SELECT Rj.Ch] is replaced by the antijoin predicate ¬(Ri.Ck = Rj.Ch) and ANDed to the merged WHERE clause obtained on steps 2 and 3 of the algorithm. The result of a query is independent of the order in which its predicates are evaluated. However, an antijoin predicate must be evaluated only after all join predicates have been evaluated. SELECT Ri.Ck FROMRi WHERERi.Ch IS NOT IN (SELECT Rj.Cm FROMRj WHERE Rj.Cn = Ri.Cp); Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  16. Processing a Type-JA Nested Query: 1 PROOF. It is shown in Section 2 that the operation of Q3 may be thought of as first fetching a tuple of Ri and all tuples of Rj whose Cn column values are the same as the Cp column value of the Ri tuple, then applying the aggregate function AGG on the Cm column of the Rj tuples to obtain a constant X, and, finally, outputting the Ck value of the Ri tuple if x = C,, column value of the Ri tuple. Now RI is a binary relation of each distinct value in the Cn column of Rj and the corresponding value obtained by applying the aggregate function AGG on the Rj tuples. Then it is clear that the query may be processed by fetching each tuple of Ri, then fetching the Rt tuple whose C1column has the same value as the Cp column of the Ri tuple, and outputting the Ck column value of the Ri tuple if the C2 column value of the Rt tuple is the same as the Ch value of the Ri tuple. But this is exactly the operation of Q4. LetQ3be SELECT Ri.Ck FROM Ri WHERE Ri.Ch = (SELECT AGG(Rj.Cm) FROM Rj WHERE Rj.Cn = Ri.Cp); And let Q4 be SELECT Ri.Ck FROM Ri WHERE Ri.Ch = (SELECT Rt.C2 FROM Rt WHERE Rt.C1 = Ri.Cp); where Rt(C1, C2) is obtained by Rt(C1, C2) = (SELECT Rj.Cn, AGG(Rj.Cm) FROM Rj GROUP BY Rj.Cn); LEMMA 2. Q3 and Q4 are equivalent; that is, they produce the same result. Lemma 2 directly leads to an algorithm which transforms a type-JA nestedquery of depth 1 to an equivalent type-J nested query of depth 1. Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  17. Rt(C1,..., Cn, Cn+1) = (SELECT C1,. . . , Cn, AGG(Cn+1) FROMR2 GROUPBY C1, . . . , Cn) ; Processing a Type-JA Nested Query: 2 Algorithm NEST-JA: 1 Generate a temporary relation Rt( C1, . . . , Cn, Cn+1) from R2 such that each Cn+1 column value of Rt is a constant obtained by applying the aggregate function AGG on the Cn+1 column of the R2 tuples which share a common value in columns C1 through Cn. In other words, the primary key of Rt is its first n columns. 2 In inner block of the initial query change all references to R2 columns in join predicates that reference R1 to corresponding Rt columns. SELECT R1. Cn+2 FROMR1 WHERER1.Cn+1 = (SELECT AGG(R2.Cn+1) FROMR2 WHERER2.C1 = R1.C1 AND R2.C2 = R1.C2 AND : R2.Cn = R1.Cn); (SELECT Rt.Cn+1 FROMRt WHERERt.C1 =R1.C1 AND Rt.C2 =R1.C2 AND Rt.Cn = R1.Cn); Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  18. type-J type-JA(AN) N N N A type-JA NEST- N -J A Processing a Type-JA Nested Query: 3 transformation of a type-JA nested query of depth 2 type-JA (NA) NEST- JA A N type-JA (AA) NEST- JA A A type-JA (AN) N A Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  19. A/N A/N A/N A/N A N N N N N N Processing a Type-JA Nested Query: 4 Algorithm for transforming a type-JA query of depth n to an equivalent type-J Algorithm NEST-JA(G) I = n (the nesting depth of the query); DO WHILE (there-is-at-least-one-straight-arc-labeled-A); IF the I-th straight arc is labeled N THEN I = I - 1; ELSE DO; Apply Algorithms NEST-JA and (NEST-N-J)to the n - I + 1 nodes to the rightof the I-th straight arc;The I-th straight arc of the resulting queryof depth I is labeled N;n = I; END; END; Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  20. Processing a Type-D Nested Query: 1 LetQ5be SELECT Ri.Ck FROM Ri WHERE (SELECT Rj.Ch FROM Rj WHERE Rj.Cn=Ri.Cp) op (SELECT Rk.Cm FROM Rk); And let Q6 be SELECT Ri.Ck FROM Ri WHERE Ri.Cp = (SELECT C1 FROM Rt) ; where Rt is obtained as: Rt(C1) = (SELECT Rj.Cn FROM Rj.RX WHERE (SELECT Rj.Ch FROM Rj.RY WHERE RY.Cn = RX.Cn) op (SELECT Rk.Cm FROM Rk) ; PROOF. As has been shown, the operation of Q5 may be thought of as fetching each tuple of Ri and checking whether the division predicate is satisfied by the Cp column value of the tuple. But what if there is a list of the Cn column values of Rj which satisfy the division predicate? Then all that needs to be done is to fetch each Ri tuple and determine whether the Cp column value of the tuple is in the list. But this is precisely the operation of Qs, since Rt is just such a list. LEMMA 3. Q5 and Q6 are equivalent; that is, they produce the same result. Lemma 3 provides the basis for Algorithm NEST-D, which transforms a general type-D nested query to an equivalent canonical two-relation query. Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  21. Processing a Type-N or Type-J Nested Query: 3 Algorithm NEST-D 1. Generate a temporary relation Rt1C1,..., Cm): Rtl(C1,...,Cm) = (SELECT Cl, ..., Cm FROM R3); Also generate Rt2(C1,..,Cn) : Rt2(C1,..., Cn) = (SELECT C1,..., Cn FROM R2) ; 2. Divide Rt2(C1,...,Cn) by Rtl(C1, ..., Cm). The result is a new temporary relation Rt3(Cm+1,..., Cn). 3.Transform the initial query to canonical - drop the query block on R3, - replace all references to columns of R2 to corresponding columns of Rt3 in the query block on R2, - eliminate the SELECT and FROM clauses of the query block on R2. The FROM clause of the resulting canonical query must now include Rt3 as well. SELECT RI. C1 FROMRI WHERE (SELECT R2. C1 FROMR2 WHERE R2.C2 = R1C2 AND R2.C3 = R1.C3AND R2.Cn = RI.Cn) = (SELECT R3. C1 FROMR3 WHERE R3.C2 = R1.C2 AND R3.C3 = R1.C3 AND R3.Cm = R1.Cm); SELECT R1.C1 FROM R1.Rt3 WHERE R1.Cm+I = Rt3.Cm+1 AND R1.Cm+2= Rt3.Cm+2 AND . . . R1.Cn = Rt3.Cn Advances in Database Query Processing Advances in Database Query Processing Aliaksei A. Holubeu Aliaksei A. Holubeu Universität Konstanz, 2005 Universität Konstanz, 2005

  22. Processing a General Nested Query: 1 • Algorithm NEST-G • Transform each type-A predicate to a simple predicate by evaluating theQ represented by the right-hand node on the query graph for the subquery(predicate). theneliminate Q and the straight arc labeled ‘A’, leading to Q from theoutermost query block. • Transform each type-JA nested subquery to an equivalent type-N or type-J subquery • by Algorithm NEST-JA(G). The query graph for the resulting type-N or type-J nested subqueryreplaces the query graph for the initialtype-JA subquery. • 3. Transform each type-D nested subquery to its canonical form by Algorithm NEST-D. • Replace the divisionpredicate by an appropriate set of join predicates, and remove • from the query graph of the initial subquerythe two right-handnodes and both the • straight and the circular arc leading to them. • 4. Transform the resulting query, which consists only of type-N and type-J subqueries, • to an equivalent canonical query by Algorithm NEST-N-J. Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  23. Processing a General Nested Query: 2 SELECT R1 . C1 FROMR1 WHERER1.C2 ISIN (SELECT R2.C2 FROM R2 WHERE R2.C3 = (SELECT AGG(R3.C3) FROMR2 WHERER3.C4 = R1.C4) AND R2.C4 IS IN (SELECT R4.C4, FROMRd) AND R1.C3 = (SELECT AGG(R5. C5) FROMR5 WHERE R5.C6 IS NOTIN (SELECT R6.C6 FROM R6)) AND (SELECT R7.C7 FROMR7 WHERER7.C8 = R1.C5) (SELECT= R8.C8 FROM R8); Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  24. Processing a General Nested Query: 3 R1.C4 = R3.C4 R3 A(C3) N(C2) R2 R1 N(C4) D R5 R1.C5 = R7.C8 R4 Nx(C6) R8 R7 R6 Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  25. R1.C4 = R3.C4 R3 A(C3) R1 R2 N(C2) D R5 R1.C5 = R7.C8 N(C4) R4 R8 R7 Nx(C6) R6 Processing a General Nested Query: 4 type-JA depth 2 type-J equivalent). NEST-JA(G) type-JA depth 1  conjunction of join predicates NEST-JA, R2.C3 = Rt1.C2AND Rt1.C1= R1. C4 where thetemporary relation Rt1 is obtained by Rt1(C1, C2) = (SELECT R3.C4, AGG(R3.C3) FROM GROUP BYR3.C4); type-N depth1  join predicate, R2.C4 = R4.C4 NEST-N-J Then type-JA depth 2  type-Jdepth 1 NEST-N-J. R1.C2 ISIN (SELECT R2.C2 FROMR2, R4, Rt1 WHERE R2. C3 = Rt1.C2AND Rt1.C1 = R1.C4AND R2.C4 = R4.C4); Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  26. R2.C3 = Rt1.C2 AND Rt1.C1 = R1.C4 AND R2.C4 = R4.C4 R2 R4 Rt1 N(C2) R1 A(C5) R1.C4 = R3.C4 D R5 R3 A(C3) R1 R2 N(C2) R1.C5 = R7.C8 Nx(C6) R8 D R5 R1.C5 = R7.C8 N(C4) R4 R7 R8 R7 Nx(C6) R6 R6 Processing a General Nested Query: 5 Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  27. R2.C3 = Rt1.C2 AND Rt1.C1 = R1.C4 AND R2.C4 = R4.C4 R2 R4 Rt1 R1 N(C2) A(C5) D R5 R1.C5 = R7.C8 R8 R7 Nx(C6) R6 Processing a General Nested Query: 6 type-A depth 2 is evaluated. type-N  SELECT AGG(R5.C5) recursive call FROM R5, R6 NEST-G WHERE ¬(R5.C6 = R6.C6); The transformed node is then evaluated to a constantX, and the nested predicateof the initial type-A subquery becomes a simple predicate, R1.C3 = x. Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  28. R2.C3 = Rt1.C2 AND Rt1.C1 = R1.C4 AND R2.C4 = R4.C4 ANDR1.C3 = x R2 R4 Rt1 N(C2) R1 R2.C3 = Rt1.C2 AND Rt1.C1 = R1.C4 AND R2.C4 = R4.C4 D R1.C5 = R7.C8 R8 R2 R4 Rt1 R1 R7 N(C2) A(C5) D R5 R1.C5 = R7.C8 R8 R7 Nx(C6) R6 Processing a General Nested Query: 7 Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  29. R2.C3 = Rt1.C2 AND Rt1.C1 = R1.C4 AND R2.C4 = R4.C4 ANDR1.C3 = x R2 R4 Rt1 N(C2) R1 D R1.C5 = R7.C8 R8 R7 Processing a General Nested Query: 6 Then, type-D subquery is evaluated so as to replace the division predicatewith a join predicate by Algorithm NEST-D. The resulting join predicate is RI. CS= Rn. Cl, where the unary relation Ra(C,) is the quotient of dividing RT(CB, CT)by RdCs The resulting query is type-J nested and Algorithm NEST-N-J can be used totransform it into its canonical equivalent, shown in the following. SELECTR1.C1 FROMR1, Rt1, Rt2 WHERER1.C2 = R2.C2AND R2.C3 = Rt1.C2AND Rt1.C1=R1.C4AND R2.C4= R4.C4AND R1.C3 =x AND R1.C5 = Rt2.C1; Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  30. Performance improvements: Consider Q3 and Q4: Let P, = 50, Pj = 30, Pt = 5,B = 6, and fi.Ni = 100. The nested-iteration method of processing Q3 is, worst case,3050 pagefetches. Q4incurs 615 page fetches. type-D nested query: Let Pi = 50, Pj = Pn = 30, pt = 5, Pk = Pts = 1, B = 4, and fi.Ni= 1000. The nested-iteration method requires,in the worst case,30051 page fetches. Processingthe type-D nested query by the algorithms shown here is 751 pageI/OS. Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

  31. Thank you for the attention!!! Advances in Database Query Processing Aliaksei A. Holubeu Universität Konstanz, 2005

More Related