Additional Estimation Considerations - Relational Database Index Design and the Optimizers - page 274

The most important variable is the average number of qualifying rows per
page . With skip-sequential, only the first row per page causes a disk read. The
rest are equivalent to sequential touches.

EstimatingSkip-Sequential I/OTime

When the number of skip-sequential touches (T) exceeds the number of leaf or
table pages (P), the elapsed time for the scan can be estimated by:

P × 10ms+(T-P) × 0.01 ms

P represents the number of pages within the index or table slice. The formula
is based on the assumption that one page is read at a time—10 ms per page.
This can be a very pessimistic assumption when few pages are skipped and the
DBMS or disk system starts to read ahead; the I/O time per page can then be as
low as 0.1 or 0.2 ms according to the page size (4 or 8K).
Let us apply this formula to the primary key index of table CUST shown
in Figure 15.4. There are 20,000 skip-sequential touches when the rows of table
INVOICE are in CNO sequence. Using the above formula, this will take
2500

× 10 ms + ( 20 , 000 − 2500 ) × 0 . 01 ms = 25 s

( QUBE : 20 , 000 × 10 ms = 200 s )

With assisted random read, the actual time can be even shorter. This was
observed when the join case study scenarios were measured on different plat-
forms. DB2 for z/OS, for instance, started to read 32 pages ahead after a few
single-page I/Os were performed (dynamic prefetch). This is why 100,000

P

F,C

STAR T INO CNO

20 MT

20 KT

Skip seq

P,C

INVOICE

CNO

20,000,000 rows

2500
leaf pages

20 KT

Skip seq

FF(IEUR > :IEUR)
= 0.1%

CUST

1,000,000 rows
100,000 pages

Figure 15.4 Natural born skip-sequential.