1 / 18

The Query Compiler

The Query Compiler. Parses SQL query into parse tree Transforms parse tree into expression tree ( logical query plan ) Transforms logical query plan into physical query plan. SQL query. parse. parse tree. convert. answer. logical query plan. execute. apply laws. Pi.

lilka
Download Presentation

The Query Compiler

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. The Query Compiler • Parses SQL query into parse tree • Transforms parse tree into expression tree (logical query plan) • Transforms logical query plan into physical query plan

  2. SQL query parse parse tree convert answer logical query plan execute apply laws Pi “improved” l.q.p pick best estimate result sizes statistics {P1,C1>...} l.q.p. +sizes estimate costs consider physical plans {P1,P2,…..}

  3. Grammar for simple SQL <Query> ::= <SFW> <Query> ::= (<Query>) <SFW> ::= SELECT<SelList>FROM<FromList>WHERE<Cond> <SelList> ::= <Attr>,<SelList> <SelList> ::= <Attr> <FromList> ::= <Relation>, <FromList> <FromList> ::= <Relation> <Cond> ::= <Cond>AND<Cond> <Cond> ::= <Tuple>IN<Query> <Cond> ::= <Attr>=<Attr> <Cond> ::= <Attr>LIKE<Pattern> <Tuple> ::= <Attr> Atoms(constants), <syntactic categories>(variable), ::= (can be expressed/defined as)

  4. Query and parse tree StarsIn( title,year,starName ) MovieStar( name,address,gender,bdate ) Query: Give titles of movies that have at least one star born in 1960 SELECT title FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE birthdate LIKE '%1960%' );

  5. Another query equivalent SELECT title FROM StarsIn, MovieStar WHERE starName = name AND birthdate LIKE '%1960%' ;

  6. Parse Tree <Query> <SFW> SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <RelName> , <FromList> AND title StarsIn <RelName> MovieStar <Condition><Condition> <Attribute> = <Attribute> <Attribute> LIKE <Pattern> starName name birthdate ‘%1960’

  7. The Preprocessor(expand query & semantic checking) • Checks against schema definition: • Relation uses • Attribute uses, resolve names ( A to R.A) • Use of types (strings, integers, dates, etc) and operators’ arguments type/arity • These preprocessing functions are called semantic checking • If all tests are passed, then the parse tree is said to be valid

  8. Algebraic laws for transforming logical query plans • Commutative and associative laws: Above laws are applicable for both sets and bags

  9. Theta-join • Commutative: • Not always associative: • On schema R(a,b), S(b,c), T(c,d) the first query can not be transformed into the second: (Why?) Because, we can’t join S and T using the condition a<d since a is an attribute of neither S nor T.

  10. Laws Involving Selection () Splitting laws Only if R is a set. The union is “set union” Order is flexible

  11. Laws Involving Selection () What about intersection? For intersection, the selection is required to be pushed to one argument.

  12. If all attributes in the condition C are in R (for binary operators)

  13. Example: • Consider relation schemas R(A,B) and S(B,C)and the expression below: (A=1 OR A=3) AND B<C(RS) • Splitting ANDA=1 OR A=3(B<C(RS)) • Push to S A=1 OR A=3(RB<C(S)) • Push to RA=1 OR A=3(R) B < C(S)

  14. Some Trivial Laws • Watch for some extreme cases: • an empty relation: • e.g., R S = S, if R =  • a selection or theta-join whose condition is always satisfied • e.g., C(R) = R, if C = true • a projection on all attributes is “better” not to be done at all!!

  15. Pushing selections • Usually selections are pushed down the expression tree. • The following example shows that it is sometimes useful to pull selection up in the tree. StarsIn(title,year,starName) Movie(title,year,length,studioName) CREATE VIEW MoviesOf1996 AS SELECT * FROM MOVIE WHERE year=1996; Query:Which stars worked for which studios in 1996? SELECT starName,studioName FROM MoviesOf1996 NATURAL JOIN StarsIN;

  16. pull selection up then push down

  17. Laws for (bag) Projection • A simple law: Project out attributes that are not needed later. I.e. keep only the input attr. and join attr. • Projections cannot be pushed below S, or either set/bag versions of and – • Example: Consider R(A,B) and S(A,C). Supp. R = {(1,2)} and S = {(1,3)}. •  A(R S) = A() butA(R) A(S) = {(1)}

  18. Example • Schema: StarsIn(title,year,starName) • Query:SELECT starName FROM StarsIn WHERE year = 1996; starName starName year=1996 year=1996 Should we can transform to  starName,year StarsIn StarsIn

More Related