Proactive Index Design - Relational Database Index Design and the Optimizers - page 67

Comparison
LRT = TR × 10 ms + TS × 0.01 ms

Absolute
LRT = TR × 10 ms + TS × 0.01 ms
+ F × 0.1 ms

LRT = Local response time

= Number of random touches
= Number of sequential touches
= Number of successful FETCHes

TR
TS
F

Figure 5.2 Quick Upper-Bound
Estimate (QUBE).

sake of completeness, the FETCH cost will normally be included in the examples
shown in this topic.

Essential Concept: Touch

When the DBMS reads one index row or one table row the cost is, by definition,
one touch : index touch or table touch. If the DBMS scans a slice of an index
or table (the rows being read are physically next to each other), reading the first
row infers a random touch . Reading the next rows take one sequential touch per
row. With current hardware, sequential touches are much cheaper than random
touches. The cost of an index touch is essentially the same as the cost of a
table touch .
We will now consider the index and table sequential touches in more detail.

ReadingaSetofSequential IndexRows

What does physically next to each other mean? All the index rows in an index are
chained together by pointers. The order is strictly defined by the key of the index.
When several index rows have the same key value, they are chained according
to the pointer value. In a traditional (and from one point of view, ideal) setup,
the chain starts in LP1 (leaf page 1), then continues through LP2 and so on.
Furthermore, (assuming 12 LPs per track—current devices usually have many
more), the leaf pages form a contiguous file; LP1-LP12 could be on the first
track of a disk cylinder, LP13-LP24 on the next cylinder, and so forth. When the
first cylinder is full, the next LPs are placed on the first track of the next cylinder
and so on. In other words, there are no other pages between the leaf pages.
Reading a set of sequential index rows—an index slice, or the rows that
correspond to a single key or range of key values—is now very fast. One disk
rotation reads several leaf pages into memory and a short seek is required only
when the position moves to the next cylinder.