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

There are at least three factors that can destroy this perfect order:
1. If there is not enough space in a leaf page for an index row being inserted,
a leaf page split must be performed. Afterwards the chain will still connect
the index rows in the correct sequence, but that sequence no longer cor-
responds to the physical sequence; some “supposedly” sequential touches
then become random touches. A reorganization of the index will restore
perfect order once more.
2. Unexpected growth may fill the original continuous area (extent or equiv-
alent). The operating system will then find another continuous area that
is linked to the first one. This is not a huge consideration but jumping to
the next extent will mean a random touch.
3. RAID 5 striping stores the first few leaf pages on one drive and then
continues on the next drive. This causes additional random reads, but
in fact the benefits of doing this are more important; with an intelligent
disk server the next leaf pages can be brought into the disk cache in
parallel from several drives, and so the I/O time per leaf page is much
reduced. Furthermore, a very active index is less likely to overload a disk
drive because the I/O requests are spread across several drives in a RAID
5 array.
Regardless of the above, two index rows are considered to be physically next to
each other (in the QUBE) if they are next to each other in the pointer chain (or
in a nonunique index, if the pointers representing the index rows are next to each
other). This simply means that the QUBE assumes all the indexes are in perfect
order; this will be discussed further in Chapter 11.
ReadingaSetofSequentialTableRows
Reading a set of sequential table rows comes in two flavors.
1. A full table scan starts with TP1 (table page 1), reads all the table rows
there, and then continues with TP2. No particular order is followed; the
table rows are simply read as they were placed in each table page.
2. A clustered index scan reads the first index row of an index slice and
then retrieves the corresponding table row. This is then repeated with the
second index row and so forth. If the index rows and the table rows are in
exactly the same sequence (cluster ratio 100%), all the table row touches
except the first one will be sequential. The table rows are not chained
together in the same way as the index rows. The order of the table rows
in a table page is irrelevant as long as the next table row to be touched
is on the same or the next physical table page.
As with indexes, the traditional way to store a table is to keep all the table
pages together in one continuous area. The factors that cause disorganization or
fragmentation are similar to those we saw with indexes, but with two differences: