Introduction - Relational Database Index Design and the Optimizers - page 7

the index had four levels; three for the original, two nonleaf and one leaf, together
with a further three for the new. When a random disk read took 30 ms, moving
one index row could add 6 × 30 ms = 180 ms to the response time of the update
transaction. It is hardly surprising that volatile columns were seldom indexed.
These days when three levels of a four-level index, the nonleaf pages, stay
in memory and a random read from a disk drive takes 10 ms, the corresponding
time becomes 2 20 ms. Furthermore, many indexes are multicolumn

indexes, called compound or composite indexes, which often contain columns
that make the index key unique. When a volatile column is the last column of
such an index, updating this volatile column never causes a move to another leaf
page; consequently, with current disks, updating the volatile column adds only
10 ms to the response time of the update transaction.

× 10 ms =

Example

A few years ago, the DBAs of a well-tuned DB2 installation having an aver-
age local response time of 0.2 s , started transaction-level exception monitoring.
Immediately, they noticed that a simple browsing transaction regularly took more
than 30 s ; the longest observed local response time was a couple of minutes .They
quickly traced the problem to inadequate indexing on a 2-million-row table. Two
problems were diagnosed:
ž A volatile column STATUS, updated up to twice a second, was absent from
the index, although it was an obvious essential requirement . A predicate
using the column STATUS was ANDed to five other predicates in the
WHERE clause.
ž An ORDER BY required a sort of the result rows.
These two index design decisions had been made consciously, based on widely
used recommendations . The column STATUS was much more volatile than most
of the other columns in this installation. This is why the DBAs had not dared to
include it in the index. They were also afraid that an extra index, which would
have eliminated the sort, would have caused problems with INSERT performance
because the insert rate to this table was relatively high. They were particularly
worried about the load on the disk drive.
Following the realization of the extent of the problem caused by these two
issues, rough estimates of the index overhead were made, and they decided to
create an additional index containing the five columns, together with STATUS at
the end. This new index solved both problems. The longest observed response
time went down from a couple of minutes to less than a second . The UPDATE
and INSERT transactions were not compromised and the disk drive containing
the new index was not overloaded.

Disk Drive Utilization

Disk drive load and the required speed of INSERTs, UPDATEs, and DELETEs
still set an upper limit to the number of indexes on a table. However, this ceiling