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

splits. As random touches to resident leaf pages take about 0.1 ms instead of
10 ms, a resident index may need no reorganization, even if it has a high insert
rate to a small area. If a fat resident index is too expensive, a semifat resident
index may be a good alternative solution.
SpecialCases
There will be occasions when one or both of the assumptions shown earlier do
not apply; the following comments should then be considered.
1. If the index is nonunique, the index keys will be followed by several
pointers, one per duplicate key. Many physical index rows may then be
as short as 10 bytes or so (a pointer and a few control characters).
If all index key values have a large number of pointers (e.g., SEX index),
the free-space requirements are reduced. The index may be in a good
shape when 67% (vs. 33%) of the free space per leaf page has been used.
If some index key values have only a single pointer, the guidelines for
unique indexes should be used to be on the safe side.
2. If a DBMS doesn't free the space of a deleted index row until index reor-
ganization takes place, the gross growth (inserts) should be used instead
of the net growth (inserts − deletes) in the formula for determining the
reorganization frequency.
VOLATILE INDEX COLUMNS
Let us consider an index that starts with an extremely volatile column
(INV THIS M, CNO, CNAME), where column INV THIS M is the amount
invoiced this month. With this index, the following SELECT, for instance, would
be extremely fast, assuming that the DBMS is able to read the index backwards.
TR = 1, TS = 500, F = 500, LRT = 65 ms
Figure 11.8 shows the index at the beginning of the month, when the index
rows are in CNO sequence. When the first invoice is created, the DBMS must
move the related index row to the end of the index. The index row for the
customer associated with the second invoice is then moved, according to the
invoice amount, to either before or after the previously moved index row. With
every new invoice, an index row may move—and the move is always forward.
The index will have a lot of leaf page splits close to the end. This is a hot
spot problem.
SQL 11.3
SELECT CNAME, CNO, INV THIS M
FROM CUST
ORDER BY INV THIS M DESC
WE WANT 500 ROWS PLEASE