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

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

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.

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

View Full Document
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.
01/11/12

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

View Full Document
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,
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)

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

View Full Document
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
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

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

View Full Document
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
Ask a homework question - tutors are online