SQL Tuning [Electronic resources] نسخه متنی

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:

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

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.

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

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.

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).

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

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.

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

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

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

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

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.