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

1. If a table row being inserted does not fit on its home page as defined
by the clustering index, the existing rows in that page are not normally
moved. Instead, the new row is placed on another page as close as pos-
sible to the home page. Additional random I/Os to this second page may
make clustering index scans slower, although these will be avoided if the
displaced table row is close to its home page because sequential prefetch
reads a number of pages at a time into the database buffer pool. Even
if sequential prefetch is not used, additional random I/Os will only take
place if the second page has been overwritten in the database buffer
pool.
2. A row may be updated such that the row becomes so long that it no
longer fits in the same page. The DBMS must then move the extended
row to another page, leaving an anchor point on the original page, which
points to the new page. This creates additional random touches when the
extended row is accessed.
Table reorganization will restore the order of a table thereby minimizing
unnecessary random touches. Consequently, table rows are considered to be phys-
ically next to each other in the QUBE if they reside on the same or consecutive
table pages. In other words, all tables, as with indexes, are assumed to be in
perfect order.
It has been necessary to make several worst-case assumptions to make the
QUBE simple enough to be used quickly and easily. A few optimistic assump-
tions were necessary as well. According to the QUBE, scanning one index or
table slice will require one random touch only , despite the issues raised above.
The database specialists should monitor the need for reorganization so that the
optimistic assumptions we make should be justified.
Counting Touches
As the touch is so important to the QUBE, we will now explain how to determine
the number of index and table touches, both random and sequential.
RandomTouches
We will first consider the difference between a disk read and a touch .Adisk
read accesses a page while a touch accesses a row. One random disk read brings
a whole page, often many rows, into the database buffer pool, but by defi-
nition, two consecutive random touches are unlikely to touch the same page.
Therefore, the QUBE elapsed time for a random touch is the same as the
average time for a random read from disk, 10 ms. Random reads are syn-
chronous, but with current fast processors the CPU time required for a random
touch can be ignored when estimating the elapsed time; it is normally less
than 0.1 ms.