Indexes and Reorganization - Relational Database Index Design and the Optimizers - page 206

Applying the general formula to our example above:
LPSR = 0 . 01

A = 10 ms

B = 0 . 1ms

ORIG = 0 . 5s

The index slice scan I/O time after the leaf page splits becomes

( 1 + ( 0 . 01 × 10 ms / 0 . 1ms )) × 0 . 5s = 2 × 0 . 5s = 1s

WHEN SHOULD AN INDEX BE REORGANIZED?

Insert Patterns

Indexes are reorganized to restore the correct physical order, which is important
for the performance of index slice scans and full index scans . Adding index rows
may create disorder in a way that depends on the insert pattern . It is important to
remember that updating a key column means removing an index row and adding
a new one in the position determined by the new index key value. We will see
in Chapter 13 that some products support nonkey index columns ; updating these
columns does not affect the position of the index row.
The following guidelines for the three basic insert patterns discussed below
are based on two assumptions:

1. The index is unique.
2. The space for a deleted index row can be used for new index rows before
reorganization.

1.NewIndexRowsAddedtoEndof Index(ever-increasingkeys)

No free space or reorganization is needed, assuming the DBMS does not split
the last leaf page when a new index row has a higher index key value than any
existing index row. However, if the index rows at the front of the index are
periodically deleted , the index may have to be reorganized in order to reclaim
the empty space (a creeping index ).
An important exception: If the index rows are variable length, free space is
needed to accommodate any increase in the length of the index rows.

2.RandomInserts

We will see later that, as far as free space and reorganization considerations are
concerned, short, medium, and long index rows should be treated differently; this
is shown in Figure 11.3. The latter is the most difficult case to handle, the former
the easiest; the discussion on the medium case will serve to explain why.