Databases Reference
In-Depth Information
As you can see, in this case the Query Optimizer decided to perform the aggregation
before the join. (Remember that, as mentioned in
Chapter 2, The Execution Engine
, the
Query Optimizer can decide to perform aggregations before or after a join, depending on
the cost.) By adding a
FORCE
ORDER
hint, as in the following query, the aggregation will
be moved to after the join, as shown in Figure 7-12.
SELECT
c
.
CustomerID
,
COUNT
(*)
FROM
Sales
.
Customer c
JOIN
Sales
.
SalesOrderHeader o
ON
c
.
CustomerID
=
o
.
CustomerID
GROUP
BY
c
.
CustomerID
OPTION
(
FORCE
ORDER
)
Listing 7-17.
Figure 7-12:
Plan with aggregation after the join.
Finally, a related statement,
SET
FORCEPLAN
, can also be used to preserve the join order,
as indicated in the
FROM
clause of a query, but it will request Nested Loops Joins only.
A difference between that and the hints shown so far is that this statement needs to be
turned on, and will stay in effect until turned off. For more information regarding the
SET
FORCEPLAN
statement, please refer to Books Online.
