Query Compiler

1. Query Compiler

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

3. 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,…..}

4. 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)

5. Query StarsIn(title,year,starName) MovieStar(name,address,gender,birthdate) Query: Give titles of movies that have at least one star born in 1960 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> starNamenamebirthdate ‘%1960’

7. Another query equivalent SELECT title FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE birthdate LIKE '%1960%' );

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

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

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

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

12. Laws Involving Selection () What about intersection?

13. Algebraic Laws involving selection For the binary operators, we push the selection only if all attributes in the condition C are in R.

14. 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)

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 any join attribute.

18. Examples for pushing projection Schema R(a,b,c), S(c,d,e)

19. starName starName year=1996 year=1996 starName,year StarsIn StarsIn Example: Pushing Projection • Schema: StarsIn(title,year,starName) • Query:SELECT starName FROM StarsIn WHERE year = 1996; Should we transform to  ? Depends! Is StarsIn stored or computed?

20. Reasons for not pushing the projection • If StarsIn is stored, then for the projection we have to scan the relation. • If the relation is pipelined from some previous computation, then yes, we better do the projection (on the fly). • Also, if for example there is an index on year for StarsIn, such index is useless in the projected relation starName,year(StarsIn) • While such an index is very useful for the selection on “year=1996”

21. Laws for duplicate elimination and grouping Try to move  in a position where it can be eliminated altogether E.g. when  is applied on • A stored relation with a declared primary key • A relation that is the result of a  operation, since grouping creates a relation with no duplicates.  absorbs 

22. Improving logical query plans • Push  as far down as possible (sometimes pull them up first). • Do splitting of complex conditions in  in order to push  even further. • Push  as far down as possible, introduce new early  (but take care for exceptions) • Combine  with to produce -joinsor equi-joins • Choose an order for joins

23. title title starname=name AND birthdate LIKE ‘%1960’ starName=name birthdate LIKE ‘%1960’  MovieStar StarsIn StarsIn MovieStar Example of improvement SELECT title FROM StarsIn, MovieStar WHERE starName = name AND birthdate LIKE ‘%1960’;

24. title starName=name name StarsIn birthdate LIKE ‘%1960’ MovieStar And a better plan introducing a projection to filter out useless attributes: