This **preview** has intentionally **blurred** parts. Sign up to view the full document

**Unformatted Document Excerpt**

Chapter 15: Algorithms for Query Processing and Optimization CHAPTER 15: ALGORITHMS FOR QUERY PROCESSING AND OPTIMIZATION Answers to Selected Exercises 15.13 Consider SQL queries Q1, Q8, Q1B, Q4, Q27 from Chapter 8. (a) Draw at least two query trees that can represented each of these queries. Under what circumstances would you use each of your query trees? (b) Draw the initial query tree for each of these queries; then show how the query tree is optimized by the algorithm outlined in section 18.3.2. (c) For each query, compare your on query trees of part (a) and the initial and final query trees of part (b). Answer: Below are possible answers for Q8 and Q27. Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE E, EMPLOYEE S WHERE E.SUPERSSN = S.SSN Q27: SELECT FNAME, LNAME, 1.1*SALARY FROM EMPLOYEE, WORKS_ON, PROJECT WHERE SSN = ESSN AND PNO = PNUMBER AND PNAME = 'ProductX' Q8's tree1: PROJECT E.FNAME, E.LNAME, S.FNAME, S.LNAME E.SUPERSSN=S.SSN JOIN EMPLOYEE E EMPLOYEE S Q8'S tree2: PROJECT CARTESIAN PRODUCT EMPLOYEE E EMPLOYEE S E.FNAME, E.LNAME, S.FNAME, S.LNAME SELECT E.SUPERSSN=S.SSN The initial query tree for Q8 is the same as tree2 above; the only change made by the optimization algorithm is to replace the selection and Cartesian product by the join in tree1. Thus, tree 1 is the result after optimization. Q27's tree1: PROJECT FNAME, LNAME, SALARY PNO=PNUMBER JOIN EMPLOYEE PROJECT SSN=ESSN JOIN SELECT PNAME="ProductX" WORKS_ON Q27's tree2: PROJECT FNAME, LNAME, SALARY PNO=PNUMBER AND SSN=ESSN AND PNAME="ProductX" SELECT EMPLOYEE PROJECT CARTESIAN PRODUCT WORKS_ON CARTESIAN PRODUCT The initial query tree of Q27 is tree2 above, but the result of the heuristic optimization process will NOT be the same as tree1 in this case. It can be optimized more thoroughly, Copyright 2007 Pearson Education, Inc. Publishing as Pearson Addison-Wesley. 1 Chapter 15: Algorithms for Query Processing and Optimization as follows: PROJECT FNAME, LNAME, SALARY PNO=PNUMBER JOIN EMPLOYEE PROJECT SSN=ESSN JOIN SELECT PNAME="ProductX" WORKS_ON The reason is that the leaf nodes could be arranged (in Step 3 of the algorithm outlined on page 613) so that the more restrictive selects are executed first. 15.14 A file of 4096 blocks is to be sorted with an available buffer space of 64 blocks. How many passes will be needed in the merge phase of the external sort-merge algorithm? Answer: We first need to compute the number of runs, n , in the merge phase.... View Full Document