Top-Down SQL Tuning

Fix for Problem 9

SELECT O.Order_ID, O.Due_Date, E.First_Notification_Date, E.Order_Event_ID, L.Line_ID, L.Item_ID

FROM Orders O, Order_Lines L, Order_Events E, Order_Line_Events LE

WHERE O.Customer_ID = :B1

AND O.Order_ID = L.Order_ID

AND O.Order_ID = E.Order_ID

AND E.Event_Type = 'LATE_SHIPMENT'

AND E.Order_Event_ID = LE.Order_Event_ID

AND L.Line_ID = LE.Line_ID

ORDER BY O.Due_Date, O.Order_ID, E.Order_Event_ID, L.Line_ID;

Robust Behavior:

1.Now guarantees one row per late-shipment event line, even under future data conditions.

2.Sorts sensibly, due date first, orders next, order events, next, order lines last.