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

SequentialTouches
A sequential touch reads the next row in physical sequence, which should either
be on the same page or the next page. As the CPU time and the I/O time overlap
with a sequential read (both the DBMS and the disk controller normally read
a number of pages ahead), the elapsed time for a sequential touch will be the
larger of these two components. The QUBE elapsed time for a sequential touch
is 0.01 ms.
When counting touches, we will apply the following rules, again in the quest
for simplicity.
1. Ignore index nonleaf pages because they are assumed to stay in the
database buffer pool or at least in the read cache of the disk server.
2. Assume that the DBMS goes directly to the first index row of an index
slice (ignore the time for binary searches or other techniques that may be
used to locate the index row in the leaf page).
3. Ignore any savings due to skip-sequential read. This can be a very pes-
simistic assumption and so will be discussed later.
4. Assume that all the indexes and tables are in perfect order. As described
above, this can be an optimistic assumption unless the state of the indexes
and tables is adequately monitored.
When counting index touches , it usually helps to visualize an index as a
minitable that has as many rows as the table to which it points, in perfect order
by the index key.
When counting table touches , we should assume that the table rows are
perfectly sorted in the table pages. This sequence depends on how the table is
reorganized. A full table scan (with N rows) can be assumed to require one
random touch and N 1 sequential touches.

The figure indicated in Figure 5.2 for sequential touches (0.01 ms) refers to
the processing necessary to be able to decide whether the row will be rejected
or accepted —all rows being examined, either in the index or the table, must at
least go through to this stage. Rejected rows will need no further processing.

−

FETCH Processing

Accepted rows, the number of which will be determined by the number of FETCH
calls issued (unless multirow FETCH is available), will require a great deal more
processing. Remember that TS has excluded this additional processing; the third
input variable, the F component of the LRT, now takes it into account. As shown
in Figure 5.2, this cost is an order of magnitude greater than the cost of TS; on
the other hand, it is very much smaller than the cost of TR.
If the QUBE is being used to compare alternative access paths, a table
scan compared to one using a particular index, for example, the F parameter
is irrelevant because it would be the same in each case; all that needs to be
considered are TR and TS as described above. If the QUBE is being used to