CSL 451 Introduction to Database Systems Query Optimization (13.1-13.4) Department of Computer Science and Engineering Indian Institute of Technology Ropar Narayanan (CK) Chatapuram Krishnan! Summary • Evaluation plan • Transformation of relational expressions – equivalent expressions – equivalence rules • commutative (selection) • associative (join) – minimal set of equivalence rules – join ordering – enumeration of equivalent expressions 10/04/15! • Statistical information for cost estimation – selection – join • Choice of evaluation plans – cost-based optimizer • join order selection • interesting sort order – heuristics in optimization • selection and projection operations as early as possible • left-deep join orders Department of Computer Science and Engineering ! Indian Institute of Technology Ropar! 2! 13.1.a Show that the following equivalences hold. Explain how you can apply them to improve the efficiency of certain queries E1 ./✓ (E2 10/04/15! E3 ) = (E1 ./✓ E2 E1 ./✓ E3 ) Department of Computer Science and Engineering ! Indian Institute of Technology Ropar! 3! 13.1.b Show that the following equivalences hold. Explain how you can apply them to improve the efficiency of certain queries ✓ (A GF (E)) = A GF ( ✓ (E)) G – is group by F – is the function A – attributes on which the aggregation is performed 10/04/15! Department of Computer Science and Engineering ! Indian Institute of Technology Ropar! 4! 13.2.d Given an instance of a relation to show that the natural left outer join is not associative. 10/04/15! Department of Computer Science and Engineering ! Indian Institute of Technology Ropar! 5! 13.4 Consider the relations r1(A, B, C), r2(C, D, E), and r3(E, F) with primary keys A, C, and E, respectively. Assume that r1 has 1000 tuples, r2 has 1500 tuples, and r3 has 750 tuples. Estimate the size and give an efficient strategy to compute r1 ./ r2 ./ r3 10/04/15! Department of Computer Science and Engineering ! Indian Institute of Technology Ropar! 6! 13.5 Consider the relations r1(A, B, C), r2(C, D, E), and r3(E, F) with no primary keys. Let V(C, r1) be 900, V(C, r2) be 1100, V(E, r2) be 50, and V(E, r3) be 100. Assume that r1 has 1000 tuples, r2 has 1500 tuples, and r3 has 750 tuples. Estimate the size and give an efficient strategy to compute r1 ./ r2 ./ r3 10/04/15! Department of Computer Science and Engineering ! Indian Institute of Technology Ropar! 7! 13.6 Suppose that a B+-tree index on building is available on relation department, and that no other index is available. What would be the best way to handle ¬(building<“W atson”) (department) 10/04/15! Department of Computer Science and Engineering ! Indian Institute of Technology Ropar! 8! 13.15 Suppose that a B+-tree index on (dept_name, building) is available on relation department. What would be the best way to handle (building “W atson”) 10/04/15! V (budget<55000) V (dept name=“M usic”) (department) Department of Computer Science and Engineering ! Indian Institute of Technology Ropar! 9! 13.19 Explain how to use a histogram to estimate the size of a selection of the form Av (r) 10/04/15! Department of Computer Science and Engineering ! Indian Institute of Technology Ropar! 10! 13.20 Suppose two relations r and s have histograms on attributes r.A and s.A respectively, but with different ranges. Suggest how to use the histograms to estimate the size of a natural join between r and s? 10/04/15! Department of Computer Science and Engineering ! Indian Institute of Technology Ropar! 11! 13.9 Consider the issue of interesting orders in optimization. Suppose you are given a query that computes the natural join of a set of relations S. Given a subset S1 of S, what are the interesting orders of S1? 10/04/15! Department of Computer Science and Engineering ! Indian Institute of Technology Ropar! 12! 13.16.a Show how to derive the following equivalence by a sequence of transformations using equivalence rules ✓1 10/04/15! V ✓2 V ✓3 (E) = ✓1 ( ✓2 ( ✓3 (E))) Department of Computer Science and Engineering ! Indian Institute of Technology Ropar! 13! 13.16.b Show how to derive the following equivalence by a sequence of transformations using equivalence rules ✓1 V ✓2 (E1 ./✓3 E2 ) = ✓1 (E1 ./✓3 ( ✓2 (E2 ))) ✓2 involves attributes of only E2 10/04/15! Department of Computer Science and Engineering ! Indian Institute of Technology Ropar! 14!
© Copyright 2024