1 / 27

CS 255: Database System Principles slides: From Parse Trees to Logical Query Plans

CS 255: Database System Principles slides: From Parse Trees to Logical Query Plans. By:- Arunesh Joshi Id:-006538558. Agenda. Conversion to Relational Algebra. Removing Sub queries From Conditions. Improving the Logical Query Plan. Grouping Associative/Commutative Operators. . Parsing.

trinh
Download Presentation

CS 255: Database System Principles slides: From Parse Trees to Logical Query Plans

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. CS 255: Database System Principlesslides: From Parse Trees to Logical Query Plans By:- Arunesh Joshi Id:-006538558

  2. Agenda • Conversion to Relational Algebra. • Removing Sub queries From Conditions. • Improving the Logical Query Plan. • Grouping Associative/Commutative Operators.

  3. Parsing • Goal is to convert a text string containing a query into a parse tree data structure: • leaves form the text string (broken into lexical elements) • internal nodes are syntactic categories • Uses standard algorithmic techniques from compilers • given a grammar for the language (e.g., SQL), process the string and build the tree

  4. Example: SQL query SELECT title FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE birthdate LIKE ‘%1960’ ); (Find the movies with stars born in 1960) Assume we have a simplified grammar for SQL.

  5. SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <RelName> <Attribute> LIKE <Pattern> nameMovieStar birthDate‘%1960’ Example: Parse Tree <Query> <SFW> SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <RelName> <Tuple> IN <Query> titleStarsIn <Attribute> ( <Query> ) starName <SFW>

  6. The Preprocessor • It replaces each reference to a view with a parse (sub)-tree that describes the view (i.e., a query) • It does semantic checking: • are relations and views mentioned in the schema? • are attributes mentioned in the current scope? • are attribute types correct?

  7. Convert Parse Tree to Relational Algebra • The complete algorithm depends on specific grammar, which determines forms of the parse trees • Here is a flavor of the approach

  8. Conversion • Suppose there are no subqueries. • SELECT att-list FROM rel-list WHERE cond is converted into PROJatt-list(SELECTcond(PRODUCT(rel-list))), or att-list(cond( X (rel-list)))

  9. SELECT movieTitle FROM StarsIn, MovieStar WHERE starName = name AND birthdate LIKE '%1960'; <Query> <SFW> SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <RelName> , <FromList> AND <Condition> movieTitleStarsIn <RelName> <Attribute> LIKE <Pattern> MovieStarbirthdate'%1960' <Condition> <Attribute> = <Attribute> starName name

  10. Equivalent Algebraic Expression Tree movieTitle  starname = name AND birthdate LIKE '%1960' X StarsIn MovieStar

  11. Handling Subqueries • Recall the (equivalent) query: SELECT title FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE birthdate LIKE ‘%1960’ ); • Use an intermediate format called two-argument selection

  12. Example: Two-Argument Selection title  StarsIn <condition> <tuple> IN name <attribute> birthdate LIKE ‘%1960’ starName MovieStar

  13. Converting Two-Argument Selection • To continue the conversion, we need rules for replacing two-argument selection with a relational algebra expression • Different rules depending on the nature of the sub query • Here is shown an example for IN operator and uncorrelated query (sub query computes a relation independent of the tuple being tested)

  14. Rules for IN   C R <Condition> X R  t IN S S C is the condition that equates attributes in t with corresponding attributes in S

  15. Example: Logical Query Plan title starName=name  StarsIn name birthdate LIKE ‘%1960’ MovieStar

  16. What if Subquery is Correlated? • Example is when subquery refers to the current tuple of the outer scope that is being tested • More complicated to deal with, since subquery cannot be translated in isolation • Need to incorporate external attributes in the translation • Some details are in textbook

  17. Improving the Logical Query Plan • There are numerous algebraic laws concerning relational algebra operations • By applying them to a logical query plan judiciously, we can get an equivalent query plan that can be executed more efficiently • Next we'll survey some of these laws

  18. Example: Improved Logical Query Plan title starName=name StarsIn name birthdate LIKE ‘%1960’ MovieStar

  19. Associative and Commutative Operations • product • natural join • set and bag union • set and bag intersection • associative: (A op B) op C = A op (B op C) • commutative: A op B = B op A

  20. Laws Involving Selection • Selections usually reduce the size of the relation • Usually good to do selections early, i.e., "push them down the tree" • Also can be helpful to break up a complex selection into parts

  21. Selection Splitting • C1 AND C2 (R) = C1 ( C2 (R)) • C1 OR C2 (R) = (C1 (R)) Uset (C2 (R)) if R is a set • C1 ( C2 (R)) = C2 ( C1 (R))

  22. Selection and Binary Operators • Must push selection to both arguments: • C (R U S) = C (R) U C (S) • Must push to first arg, optional for 2nd: • C (R - S) = C (R) - S • C (R - S) = C (R) - C (S) • Push to at least one arg with all attributes mentioned in C: • product, natural join, theta join, intersection • e.g., C (R X S) = C (R) X S, if R has all the atts in C

  23. Pushing Selection Up the Tree • Suppose we have relations • StarsIn(title,year,starName) • Movie(title,year,len,inColor,studioName) • and a view • CREATE VIEW MoviesOf1996 AS SELECT * FROM Movie WHERE year = 1996; • and the query • SELECT starName, studioName FROM MoviesOf1996 NATURAL JOIN StarsIn;

  24. Remember the rule C(R S) = C(R) S ? The Straightforward Tree starName,studioName year=1996 StarsIn Movie

  25. starName,studioName starName,studioName starName,studioName year=1996 year=1996 year=1996 year=1996 StarsIn StarsIn Movie StarsIn Movie push selection up tree push selection down tree Movie The Improved Logical Query Plan

  26. Grouping Assoc/Comm Operators • Groups together adjacent joins, adjacent unions, and adjacent intersections as siblings in the tree • Sets up the logical QP for future optimization when physical QP is constructed: determine best order for doing a sequence of joins (or unions or intersections) U D E F U D E F U A B C A B C

  27. Thank You

More Related