Adaptive Server performs deferred index updates when the update affects the index used to access the table or when the update affects columns in a unique index. In this type of update, Adaptive Server:
Deletes the index entries in direct mode
Updates the data page in direct mode, writing the deferred insert records for the index
Reads the log records for the transaction and inserts the new values in the index in deferred mode
Deferred index insert mode must be used when the update changes the index used to find the row or when the update affects a unique index. A query must update a single, qualifying row only once—deferred index update mode ensures that a row is found only once during the index scan and that the query does not prematurely violate a uniqueness constraint.
The update in Figure 22-8 changes only the last name, but the index row is moved from one page to the next. To perform the update, Adaptive Server:
Reads index page 1133, deletes the index row for “Greene” from that page, and logs a deferred index scan record.
Changes “Green” to “Hubbard” on the data page in direct mode and continues the index scan to see if more rows need to be updated.
Inserts the new index row for “Hubbard” on page 1127.
Figure 22-8 shows the index and data pages prior to the deferred update operation, and the sequence in which the deferred update changes the data and index pages.
Figure 22-8: Deferred index update
Assume a similar update to the titles table:
update titles
set title = "Computer Phobic’s Manual",
advance = advance * 2
where title like "Computer Phob%"
This query shows a potential problem. If a scan of the nonclustered index on the title column found “Computer Phobia Manual,” changed the title, and multiplied the advance by 2, and then found the new index row “Computer Phobic’s Manual” and multiplied the advance by 2, the advance wold be very skewed against the reality.
A deferred index delete may be faster than an expensive direct update, or it may be substantially slower, depending on the number of log records that need to be scanned and whether the log pages are still in cache.
During deferred update of a data row, there can be a significant time interval between the delete of the index row and the insert of the new index row. During this interval, there is no index row corresponding to the data row. If a process scans the index during this interval at isolation level 0, it will not return the old or new value of the data row.
