Databases Reference
In-Depth Information
If you do need to change the join order of a query for some reason, you can try starting
with the join order recommended by the Query Optimizer, and change only the part
which you think is suffering from a problem, such as cardinality estimation errors. You
can also follow the practices that the Query Optimizer itself would follow, as explained
in
Chapter 2, The Execution Engine
. For example, if you are forcing a Hash Join, select the
smallest table as the build input, or if you're forcing a Nested Loops Join, use small tables
in the outer input and the tables with indexes as the inner input. You could also start
by joining small tables first, or tables that can help to filter out the most possible number
of rows.
Let me show you an example. The query in Listing 7-12, without hints, will show you the
plan on Figure 7-8.
SELECT
FirstName
,
LastName
FROM
Person
.
Contact
AS
C
JOIN
Sales
.
Individual
AS
I
ON
C
.
ContactID
=
I
.
ContactID
JOIN
Sales
.
Customer
AS
Cu
ON
I
.
CustomerID
=
Cu
.
CustomerID
WHERE
Cu
.
CustomerType
=
'I'
Listing 7-12.
Figure 7-8:
Execution plan without hints.
