Lecture5 - Lecture 5 on Query Optimization This lecture...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon
01/11/12 Lecture 5 on Query Optimization This lecture demonstrates the techniques of optimizing SQL query command for efficiency performance. The optimizer will transform SQL query into an equivalent SQL query in the form of relational algebra with less cost. It shows how to apply heuristics rules in reducing the cost of query.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
01/11/12 Query Optimizer We can interpret an expression of relational algebra not only as the specification of the semantics of a query, but also as the specification of sequence of operations. From this viewpoint, two expressions with the same semantics can describe two different sequences of operations. Given: Relation EMP(Empnum, Name, Sal, Tax, Mgrnum, Deptnum) Relation DEPT(Deptnum, Name, Area, Mgrnum) Relation SUPPLIER(Snum, Name, City) Relation SUPPLY(Snum, Pnum, Deptnum, Quan) PJ NAME, DEPTNUM SL DEPTNUM=15 EMP = SL DEPTNAM=15 PJ NAME, DEPTNUM EMP condition SL DEPTNUM PJ DEPTNUM EMP (projected data must be in selected data) Are equivalent expressions but define two different sequences of operations.
Background image of page 2
01/11/12
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
01/11/12 The operator tree of an expression of relational algebra can be regarded as the parse tree of the expression itself, assuming the following grammar: R -> identifier R -> (R) R -> un_op R R -> R bin_op R Un_op -> SL F | PJ A Bin_op -> CP | UN | DF | JN F | NJN F | SJ F | NSJ F Two relations are equivalent when their tuples represent the same mapping from attribute names to values,
Background image of page 4
01/11/12 Commutativity of unary operations: U 1 U 2 R <->U 2 U 1 R Commutativity of operands of binary operations R B S <-> S B R Associativity of unary operations: U R <-> U 1 U 2 R Distributivity of unary operations with respect to binary operations: U (R B S) -> U(R) B U(S) Factorization of unary operations (this tranforsmation is the inverse distributivity): U (R) B U(S) -> U (R B S)
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
01/11/12 Commutativity of unary operations SL F1 SL F2 R SL F2 SL F1 R SL F1 PJ A2 R PJ A2 SL F1 R Attr(F1) A2 Commutativity and Associativity of binary operations R UN S S UN R R CP S S CP R R JN F S S JN F R (R UN S) UN T R UN (S UN T) (R CP S) CP T R CP (S CP T) Idempotence of unary operations PJ A R PJ A1 PJ A2 R A A1 A A2 SL F R SL F1 SL F2 R F=F1 F2
Background image of page 6
01/11/12 Distributivity of unary operations SL F (R UN S) (SL F R) UN (SL F S) SL F (R DF S) (SL F R) DF (SL F S) SL F (R SJ F3 S) (SL
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page1 / 44

Lecture5 - Lecture 5 on Query Optimization This lecture...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online