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

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;