Databases Reference
In-Depth Information
In summary, keep in mind that, except for a few cases where per-index plans are
mandatory, the Query Optimizer can choose between a per-row and per-index plan
on an index-by-index basis, so it is even possible to have both maintenance choices in
the same execution plan.
Halloween protection
Halloween protection refers to a problem which appears in certain update operations,
and which was found more than thirty years ago by researchers working on the System R
project (mentioned in
Chapter 1, Introduction to Query Optimization
) at the IBM Almaden
Research Center. The System R team was testing a query optimizer when they ran a query
to update the salary column on an
Employee
table. The query was supposed to give a
10% raise to every employee with a salary of less than $25,000 but, to their surprise, no
employee had a salary under $25,000 after the update query was completed. They noticed
that the query optimizer had selected the salary index and had updated some records
multiple times, until they reached the $25,000 salary. Since the salary index was used to
scan the records, when the salary column was updated, some records were moved within
the index and were then scanned again later, updating those records more than once. The
problem was called Halloween problem simply because it was discovered on Halloween
around 1976 or 1977.
As I mentioned at the beginning of this section, update operations have a read section
followed by an update section, and that is a crucial distinction to bear in mind at this
stage. To avoid the Halloween problem, the read and update sections must be completely
separated; the read section must be completed in its entirety before the write section is
run. I'll show you how SQL Server avoids the Halloween problem in the next example.
Run the statement in Listing 6-6 to create a new table.
