Databases Reference
In-Depth Information
When a large number of records are being updated, the Query Optimizer may choose a
per-index plan, which the following query will demonstrate, by creating the per-index
plan shown in Figures 6-5 and 6-6.
DELETE
FROM
Sales
.
SalesOrderDetail
WHERE
SalesOrderDetailID
<
43740
Listing 6-5.
In a per-index update, the base table is updated first, which is shown by the Clustered
Index Delete operator in Figure 6-5.
In the second part of the plan, which is shown in Figure 6-6, a Table Spool operator is
used to read the data of the key values of the indexes to be updated, and then a Sort
operator sorts the data in the order of the index. Later, an Index Delete operator updates
a specific non-clustered index in one operation (the name of which you can see on the
graphical plan). Although, the table spool is listed twice in the plan, it is actually the same
operator being reused. Finally, the Sequence operator makes sure that each Index Delete
operation is performed in sequence, as shown from top to bottom.
Figure 6-5:
Right part of the per-index plan.
Figure 6-6:
Left part of the per-index plan.
