Outer joins are procedural. The SQL Optimizer cannot do reorders of the join sequence without altering the semantics of the join. Therefore, outer joins are executed as written, that is to say depth-first, left-to-right. In the preceding examples, T2 is joined to T3 and the result is joined to T1. The order of predicate evaluation is discussed in more detail on Order of Predicate Evaluation
You should strive to write the joined table in the most efficient order and place predicates in the first join in which they can be evaluated. For example, as previously shown, the restriction on ORDER_DATE could have been added to the outer ON clause, but then it could not be used to limit the previous join, and all ORDERS rows would be joined to all LINE_ITEM rows, only to have many of those rows rejected in the next join step.
As shown in the following example, if you add a restriction on T1.CUSTNO to return the row for a specific customer, this restriction is not applied until all the ORDERS and LINE_ITEM rows have been joined:
SELECT T1.NAME, VALUE (T2.AMOUNT, 0), COUNT(DISTINCT T3.ORDNO)
FROM CUSTOMER T1 LEFT JOIN (ORDERS T2 INNER JOIN LINE_ITEM T3
ON T2.ORDNO = T3.ORDNO AND
T2.ORDER_DATE = CURRENT DATE)
ON T1.CUSTNO = T2.CUSTNO AND
T1.CUSTNO = :CUSTNO AND
GROUP BY T1.NAME, T2.AMOUNT;
Rather than doing the previous, it is more efficient to write:
SELECT T1.NAME, VALUE (T2.AMOUNT, 0), COUNT(DISTINCT T3.ORDNO)
FROM (CUSTOMER T1 LEFT JOIN ORDERS T2
ON T1.CUSTNO = T2.CUSTNO AND
T1.CUSTNO = :CUSTNO
T2.ORDER_DATE = CURRENT DATE) INNER JOIN LINE_ITEM T3
ON T3.ORDNO = T2.ORDNO
GROUP BY T1.NAME, T2.AMOUNT;
|
Copyright © 2015 CA Technologies.
All rights reserved.
|
|