Reactive Index Design - Relational Database Index Design and the Optimizers - page 117

This suggests that processing is mostly sequential. Sequential scans are often
CPU bound. Random processing is CPU bound only if all the pages accessed
are in memory. In addition, the report shows that about 100,000 pages were read
with sequential prefetch. These pages can be either leaf pages or table pages, or
both. It's possible, for instance, that a very thick index slice and a very thick table
slice are read, the index and table rows being in the same order. Nevertheless,
most rows are rejected. If we assume that a sequential touch takes roughly 10 s

(this will be fully discussed in Chapter 15), the total number of touches must be
roughly

µ

14 , 000 , 000 µ s
10 touch = 1 , 400 , 000 touches

assuming that the number of random touches is much lower than the number of
sequential touches.
From this, it should be quite easy to find the SELECT(s) that may gener-
ate massive sequential scans, more than 100,000 pages and probably more than
1,000,000 rows.
The tuning potential (assuming the 2-s other wait is caused by CPU queuing)
is then almost 16 s. It should be possible to find an access path that reads much
thinner slices.
If the slow access path contains a full index scan or a full table scan, would
that not have been noticed already in the quick EXPLAIN review? Not neces-
sarily. The access path may be using an index with one matching column for a
range predicate. With the worst input the index slice to be scanned may, in fact,
be the whole index. Figure 7.8 shows an example of such a case.
The optimizer may choose the index (IDATE, CNO) if it assumes a small
filter factor for the predicate IDATE > :IDATE. As IDATE is the only matching
column (CNO cannot participate in the matching process because the first column

s /

µ

SELECT IDATE, LNAME

MC = 1

FROM
WHERE

INVOICE

CNO = :CNO

AND
IDATE > :IDATE

ORDER BY IDATE

IDATE, CNO

INVOICE

Figure 7.8 Whole index may be scanned even though MC = 1.