FORCE ORDER

The FORCE ORDER hint can give the user full control over the join and aggregation placement 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. Also, notice that the ANSI-style join hints explained previously can 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 extreme caution. As explained earlier in this chapter, finding an optimum join order is a critical and challenging part of the query optimization process 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 query, such as...