SQL Tuning

Appendix A. Exercise Solutions

When I rest, I rust [Rast ich, so rost ich].

—Anonymous German proverb

This appendix contains solutions to the exercises in Chapter 5 through Chapter 7.

Chapter 5 Exercise Solutions

Following are the solutions to the exercises in Section 5.5.

Exercise 1

Figure A-1 shows the solution to Exercise 1.

Figure A-1. Solution to Exercise 1

The subtlest aspect of this exercise is that you need to notice that you do not need queries (other than the total table rowcounts) to find the filter ratios for the R and D nodes. From the exact matches on uniquely indexed names for each of these, a single match for R and an IN list for D, you can deduce the ratios. You just need to calculate 1/R and 2/D, where D and R are the respective rowcounts of those tables, to find their filter ratios. Did you remember to add the * to the filter ratio on R to indicate that it turns out to be a unique condition? (This turns out to be important for optimizing some queries!) You would add an asterisk for the condition on D, as well, if the match were with a single name instead of a list of names.

The other trick to notice is that, by the assumption of never-null foreign keys with perfect referential integrity, the rowcounts of the joins would simply equal the rowcounts of the detail tables. Therefore, the detail join ratios are simply d/m, where d is the rowcount of the upper detail table and ...