B.2 Solving the Query Diagram

After you've reduced the detail-filled query to an abstract join diagram, you are 80% of the way to finding the best execution plan, just as math word problems usually become trivial once you convert them to symbolic form. However, you still must solve the symbolic problem. Using the methods of Chapter 6, solve the problem abstracted in Figure B-5:

1. Choose the best driving table. By far, the best ( closest to 0) filter ratio is on C , so choose C as the driving table.

2. From C , you find no downward joins, so you choose the only upward join, to O , placing O second in the join order.

Even if there were downward joins available from C , you would still consider joining to O early, since the detail join ratio to O is less than 1.0 and since O has a good filter itself.

3. From O , you find no unused downward joins, so you choose the only upward join, to OD , placing OD third in the join order.

4. From OD , you find two unused downward joins, to S and to P . There are no more simple filters on the remaining nodes, but there is a hidden join filter in the join to S , since the master join ratio on that join is less than 1.0. Therefore, join to S next , placing S fourth in the join order.

If there were a filter on node P , you would make the implicit filter OD.Shipment_ID IS NOT NULL (which is implied by the master join ratio to S being less than 1.0) explicit, so you could pick up that filter early without joining to S and reach the filter on P after getting the benefit of that NOT NULL filter, without paying the added price of joining to S before P .

5. The remaining nodes, A and P , are both unfiltered , are reachable directly with joins from tables already reached, and have master join ratios equal to 1.0, so it makes no difference which order you join to these last two nodes. Just to make the rest of the problem concrete, arbitrarily choose A as the fifth in the join order and then choose P as the last. This leads you to the optimum join order of (C, O, OD, S, A, P) .

6. Given the join order, specify the full execution plan, following the rules for robust execution plans, in the optimum join order:

   1. Drive to the first table, Customers , on an index on the filter column, Phone_Number , with a query modified if necessary to make that index accessible and fully useful.

   2. With nested loops , join to Orders on an index on the foreign key Customer_ID .

   3. With nested loops, join to Order_Details on an index on the foreign key Order_ID .

   4. With nested loops, join to Shipments on its primary-key index on Shipment_ID .

   5. With nested loops, join to Addresses on its primary-key index on Address_ID .

   6. With nested loops, join to Products on its primary-key index on Product_ID .

This completes the second step in the tuning process: finding the execution plan that you want. Next, you need to see which execution plan you actually get, on all three databases, since this example illustrates SQL that is designed to run on any of the three.