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

INV_THIS_M CNO

0 4

0 10

P,C

INV_THIS_M,
CNO,
CNAME

CNO 0 14

0 15

0 19

CUST

0 22

........ .....

Figure 11.8 A split-bound index—index row moves not random.

If the index is reorganized only once a month, LPSR will get quite high.
In theory, the TOP 500 query could require as many as 500 random touches
(if every index touch was random), although the actual number of TRs would
probably be much less. Real problems would come with SELECTs for which an
index starting with INV THIS Mwas not a three-star index. Then, one FETCH
might need thousands of index touches, many of which would be random when
the LPSR is high.
There are at least three feasible solutions for the hot spot problem:

1. Make the index resident; P = 0, no reorganization. If there are 1,000,000
customers, the size of the index may be 60 MB. If the cost of memory is
U.S.$1000 per gigabyte a month, the cost of the resident index is $60 per
month. However, in 32-bit systems virtual storage constraints may make
this solution unattractive.
2. Create a resident semifat index (INV THIS MONTH); P

0, no reor-
ganization. This index does not consume much memory—probably less
than 10 MB because it is not unique. As with a normal semifat index,
each FETCH requires a table touch. Therefore, this alternative makes
sense only if there are many index touches per FETCH (and not many
FETCHes): the index touches remain fast no matter how high the LPSR.
3. Reorganize the fat index often enough to keep the LPSR low; perhaps
every night. If there are 1,000,000 customers, the elapsed time for index
reorganization may be less than a minute.
Volatile index columns do not cause split problems if the index row move-
ments are random . Consider the following: An index starting with ZIPCODE
on table CUST with 1,000,000 rows; 100,000 customers move location to a new
ZIPCODE per year while the number of customers grows by only 2%. The LPSR

=