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

table scan for both SELECTs. One of the tables, CUST, had 1,000,000 long rows
(400 bytes) in 111,000 4K pages; the other, INVOICE, had 4,000,000 short rows
(80 bytes) in 77,000 4K pages.
The tables were not compressed and all the columns were of fixed length.
The two single WHERE clauses were extremely simple. Lock size was page, iso-
lation level CS. Additional predicates, particularly complex ones, would increase
the CPU times as would longer INVOICE rows, variable length rows, and com-
pression.
The results, using an old and busy server (100 mips per processor) were:
SQL CPU time for a full table scan
CUST (1 million rows, 111,000 4K pages): 2.5 s, 2 sperrow

INVOICE (4 million rows, 77,000 4K pages): 5 s, 1 . 25 µ sperrow
Assuming that the number of rows and the number of pages are the only signif-
icant factors, we can find the coefficients for these two variables:
1,000,000 X

. 5

µ

+ 111,000 Y = 2 . 5s

4,000,000 X + 77,000 Y = 5s

X = CPU time per row = 1 µ s
Y = CPU time per page = 13 µ s
The relative figures for X and Y clearly show the impact that the number and
length of the columns have on the CPU time.
Our second example used a large, busy server (440 mips per processor); a
table with 13,000,000 short rows in 222,000 4K pages was scanned, again all
rows being rejected. The table was compressed, and it contained variable-length
columns, but again the single predicate was extremely simple.
The reported CPU time was 9 s, 0

sperrow.
In a third example using a small server (750 MHz per processor), the CUST
table with 1,000,000 long rows (400 bytes) was scanned and 1078 rows FETCHed
and sorted. The WHERE clause had two simple predicates. The reported CPU
time was 10.4 s.
The TS CPU time was 10 . 4s − X, where X is the CPU time for the FETCHes
and the sort (1078 rows for each). Using our CPU time coefficients,
X = 1078 × 110 µ s = 0 . 1s
Thus, the CPU time per sequential touch for the long rows turns out to be 10 µ s.
It would seem that 5 µ s is a reasonable default value to use, but it would be
wise to calibrate the coefficient for each platform by measuring the CPU with
both long and short rows.

. 7

µ

CPU Time per Random Touch

There are several reasons why a random touch requires much more CPU time
than a sequential touch: