Databases Reference
In-Depth Information
On the other hand, a scalar aggregation will always use a Stream Aggregate operator.
Trying to force a Hash Aggregate on a scalar aggregation, as in the following query, will
trigger the compilation error shown in Listing 7-5, complaining about the hints defined in
the query.
SELECT
COUNT
(*)
FROM
Sales
.
SalesOrderDetail
OPTION
(
HASH
GROUP
)
Listing 7-11.
FORCE ORDER
The
FORCE
ORDER
hint can give the user full control over the join and aggregation place-
ment in an execution plan. Specifically, the
FORCE
ORDER
hint asks the Query Optimizer
to preserve the join order and aggregation placement as indicated by the query syntax.
Notice, also, that the
ANSI
-style join hints explained before can also give you control
of the join order, in addition to control over the choice of the join algorithm. Both the
FORCE
ORDER
and
ANSI
-style join hints are very powerful, and because of that they need
to be used with caution. As explained earlier in this topic, inding an optimum join order
is a critical part of the query optimization process, and also a challenging one, because
the sheer number of possible join orders can be huge even with queries involving only
a few tables. What this boils down to is that, by using the
FORCE
ORDER
hint, you are
attempting to optimize the join order yourself.
You can use the
FORCE
ORDER
hint to obtain any form of query, like left-deep trees, bushy
trees or right-deep trees, explained in
Chapter 1, Introduction to Query Optimization
. The
Query Optimizer will usually produce a left-deep tree plan, but you can force bushy trees
or right-deep trees by doing things like changing the location of the
ON
clause on the join
predicate, using subqueries, parenthesis, etc. Be aware that forcing join order does not
affect the simplification phase of query optimization, and some joins may still be removed
if needed, as explained in
Chapter 5, The Optimization Process
.
