SQL Tuning

Appendix B. The Full Process, End to End

Do not delay, Do not delay: the golden moments fly!

—Henry Wadsworth Longfellow Masque of Pandora (Pt. VII)

Throughout the book, there are examples that illustrate each step of the process in detail, but I have not yet followed a single example through the entire process. If you like seeing whole processes from end to end and working from those examples, this appendix is for you.

The example in this appendix follows a query that is just complex enough to illustrate the main points that come up repeatedly, while having something wrong that needs fixing. Imagine that the following query were proposed for an application designed to run well on Oracle, DB2, and SQL Server, and you were asked to pass judgement regarding its optimality on those databases and to propose changes to tune it as needed:

SELECT C.Phone_Number, C.Honorific, C.First_Name, C.Last_Name, C.Suffix, 
C.Address_ID, A.Address_ID, A.Street_Addr_Line1, A.Street_Addr_Line2, 
A.City_Name, A.State_Abbreviation, A.ZIP_Code, OD.Deferred_Ship_Date, 
OD.Item_Count, P.Prod_Description, S.Shipment_Date 
FROM Orders O, Order_Details OD, Products P, Customers C, Shipments S, 
Addresses A
WHERE OD.Order_ID = O.Order_ID
AND O.Customer_ID = C.Customer_ID
AND OD.Product_ID = P.Product_ID
AND OD.Shipment_ID = S.Shipment_ID
AND S.Address_ID = A.Address_ID
AND C.Phone_Number = 6505551212
AND O.Business_Unit_ID = 10
ORDER BY C.Customer_ID, O.Order_ID Desc, S.Shipment_ID, OD.Order_Detail_ID;

Reducing the Query ...