Databases Reference
In-Depth Information
In the next few sections, I will discuss hints affecting joins, join order, aggregations,
index scans or seeks, views, and so on. Note that, with a very simple database like
AdventureWorks
, the Query Optimizer will most likely give you an optimal plan
for all of the examples in this chapter, so I am just looking for alternate plans for
demonstration purposes.
Joins
We can explicitly ask the Query Optimizer to use any of the available join algorithms:
Nested Loops, Merge and Hash Join. We could do this at the query level, in which case
all the existing joins in the query will be affected, or we can specify it at the join level,
impacting only that join. However, this last choice will also impact the join order, as will
be explained in the
FORCE
ORDER
section.
Let's focus on join hints at the query level first; in this case, the join algorithm is speci-
fied using the
OPTION
clause. You can also specify two of the three available joins, which
basically asks the Query Optimizer to exclude the third physical join operator from
consideration. The decision between which of the remaining two joins to use will be
cost-based. For example, the following unhinted query will produce the plan in Figure 7-1,
which uses a Hash Join.
SELECT
FirstName
,
LastName
FROM
Person
.
Contact
AS
C
JOIN
Sales
.
Individual
AS
I
ON
C
.
ContactID
=
I
.
ContactID
Listing 7-1.
