The Query Compiler Parses SQL query into parse

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 tree convert logical query plan apply laws “improved” l. q. p estimate result sizes l. q. p. +sizes consider physical plans {P 1, P 2, …. . } answer execute Pi statistics pick best {P 1, C 1>. . . } estimate costs

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

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

Another query equivalent SELECT title FROM Stars. In, Movie. Star WHERE star. Name = name AND birthdate LIKE '%1960%' ;

Parse Tree <Query> <SFW> SELECT <Sel. List> FROM <Attribute> <From. List> WHERE <Condition> <Rel. Name> , <From. List> title Stars. In AND <Rel. Name> Movie. Star <Condition> <Attribute> star. Name = <Attribute> name <Condition> <Attribute> LIKE <Pattern> birthdate ‘%1960’

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

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

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.

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

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

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

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) A=1 OR A=3 ( B < C(R S)) 1. Splitting AND 2. Push to S A=1 OR A=3 (R B < C(S)) 3. Push to R A=1 OR A=3 (R) B < C(S)

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!!

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. Stars. In(title, year, star. Name) Movie(title, year, length, studio. Name) CREATE VIEW Movies. Of 1996 AS SELECT * FROM MOVIE WHERE year=1996; Query: Which stars worked for which studios in 1996? SELECT star. Name, studio. Name FROM Movies. Of 1996 NATURAL JOIN Stars. IN;

pull selection up then push down

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)}

Example • Schema: Stars. In(title, year, star. Name) • Query: SELECT star. Name FROM Stars. In WHERE year = 1996; star. Name year=1996 Should we can transform to star. Name, year Stars. In