Here we will start to use joins. To keep things simple we will use the * in the SELECT list. Note that this will ensure that columns will be required from both tables. We will perform the following join:
SELECT * FROM customers C INNER JOIN accounts A ON C.customer_no = A.customer_no
WHERE C.customer_no = 1000 The query plan is shown in Figure 4.35.
Figure 4.35: Graphical query execution plan for a nested loops inner join
The indexes present on the Customers and Accounts tables are both nonclustered indexes on the customer_no column. In the case of the Customers table the nonclustered index is unique. We can see that, reading from right to left, there are two inputs to the plan with the parent being the Nested Loops physical operator. If the mouse pointer is placed over this operator, the resulting display explains how it processes the join. This is shown in Figure 4.36.
Figure 4.36: Graphical query execution plan showing the nested loops description It states: For each row in the top (outer input), scan the bottom (inner) input and output matching rows. So this is our classic nested loops join described earlier in the chapter. The query optimizer has determined that the (unique) index on the customer_no column in the Customers table is highly selective, since it can, at most, return one row. The Customers table has therefore been chosen to be the outer input. So, for each qualifying row in the outer input—that is, for each Customer row—the customer number will be used to access the inner input—that is, the Accounts table. The nonclustered index on the customer_no column in the Accounts table will be used to fetch the matching rows from the Accounts table. Note the Set statistics IO display, which is as follows:
Table 'accounts'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.
physical reads 0, read-ahead reads 0.
Often the inner table will have a scan count greater than one, but, because the outer input can only produce a maximum of one row, the scan count is, in fact, one. This means that the query optimizer estimates that the inner table will be only be accessed once. Suppose we changed the query, as follows:
SELECT * FROM customers C INNER JOIN accounts A ON C.customer_no = A.customer_no
WHERE C.customer_no BETWEEN 1000 AND 1003
In this case, four customers are returned from the Customers table, and the scan count of the Accounts table is, therefore, four, as shown in the following code:
Table 'accounts'. Scan count 4, logical reads 16, physical reads 0, read-ahead reads 0.
Table 'customers'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0.
Why did the query optimizer choose a nested loops join? Ultimately because it decided that it was the most efficient plan in terms of cost. There was a highly selective index on both tables. However, suppose we increase the range of customer numbers, as follows:
SELECT * FROM customers C INNER JOIN accounts A ON C.customer_no = A.customer_no
WHERE C.customer_no BETWEEN 1000 AND 1999
Now, if we check the query plan, we can see that the query optimizer has decided that the nested loops join method is not the most efficient. There are 1,000 customers that satisfy the query and a nested loop would result in the inner table being accessed 1,000 times. The query optimizer has decided that a hash join is a better bet. The query plan is shown in Figure 4.37.
Figure 4.37: Graphical query execution plan showing a hash join
Again, we observe two inputs, but we can see that these are table scans. The parent operator for the table scans is the Hash Match physical operator. If the mouse pointer is placed over this operator, the resulting display explains how it processes the join. This is shown in Figure 4.38.
Figure 4.38: Graphical query execution plan showing the hash join
It states: Use each row from the top input to build a hash table, and each row from the bottom input to probe into the hash table, outputting all matching rows. This is the hash join we described earlier in this chapter. The Set statistics IO output is shown in the following code. Notice that each table has a scan count of just one.
Table 'accounts'. Scan count 1, logical reads 1570, physical reads 0, read-ahead reads 0.
Table 'customers'. Scan count 1, logical reads 840, physical reads 0, read-ahead reads 0.
Now both tables are accessed via a table scan. Does this mean that the indexes are redundant? At first sight you might think the answer would be yes—but you would be wrong! The reason is this: The query optimizer needs to know how many customers satisfy the query. It uses this information to work out the table to use for the build input and the table to use for the probe input and then, ultimately, the cost. Without these indexes it would need column statistics. We have none, and we have not allowed the query optimizer to create them automatically.
Therefore, with no indexes, there are no statistics and the query optimizer might choose an inefficient plan.
When is the query optimizer likely to choose a merge join? Let us change the query so there is a requirement for the output to be sorted on the customer_no column, as follows:
SELECT * FROM customers C INNER JOIN accounts A ON C.customer_no = A.customer_no
WHERE C.customer_no BETWEEN 1000 AND 1999 ORDER BY c.customer_no
Let us also ensure that a clustered index on customer_no is created on each table. We have now created two inputs that are effectively sorted on customer_no. If we execute the query, the resulting query plan would be as shown in Figure 4.39. The query optimizer has still decided that a hash join is the most efficient method.
Figure 4.39: Graphical query execution plan showing the hash join
If we force a merge join strategy (we will see how later) we can compare the costs, as shown in the following chart.
•
Join Type Estimated Cost Logical IO CPU (ms)
Hash 1.54 2,408 120
Merge (M:M) 1.61 7,409 631
The hash join is a clear winner, so the query optimizer created the most efficient plan. But why was the merge join more expensive? After all, the sorts are effectively already done. The clue is in the Set statistics IO output, which is as follows:
Table 'Worktable'. Scan count 1999, logical reads 5000, physical reads 0, read-ahead reads 0.
Table 'accounts'. Scan count 1, logical reads 225, physical reads 0, read-ahead reads 0.
Table 'customers'. Scan count 1, logical reads 62, physical reads 0, read-ahead reads 0.
A worktable has been used that increased the cost. Why? Because the clustered index we created on the Customers table was not unique. The query optimizer used a many-to-many merge join and hence a worktable was used. Note that when the mouse pointer is passed over the Merge Join operator, the pop-up window will contain argument text, which will specify that the merge join was many-to-many. What happens if we recreate the clustered index as a unique clustered index? The query plan is shown in Figure 4.40.
Figure 4.40: Graphical query execution plan showing merge join
A merge join has been used. The costs Merge (1:M) are shown in the following chart, compared with the previous costs.
•
Join Type Estimated Cost Logical IO CPU (ms)
Hash 1.54 2,408 120
Merge (M:M) 1.61 7,409 631 Merge (1:M) 1.20 287 60
We can see that by being careful with our index creation we have enjoyed a not
inconsiderable cost reduction. Hopefully, most of you will use primary key constraints and so, in the case above, the index would have been created as a unique index automatically.