Database Reference
In-Depth Information
P
30%
20%
10%
F = 33%
Growth before
REORG
5%
10%
15%
20%
Figure 11.7
Reorganization guidelines.
has occurred. This value, 8.3%, is the growth permissible before the index should
be reorganized (to meet the LPSR objective of 1%, which, remember, will keep
the index slice I/O time down to no more than twice the ORIG). Values of G for
different values of P are shown on the graph in Figure 11.7.
Thus, the Growth before REORG (G) formula or Figure 11.7 can be used,
for a given P, to predict how frequently an index should be reorganized. Alter-
natively, if the reorganization interval has been specified, they may be used to
choose a value for P. Let us assume that the index in our example (daily growth
1%) can be reorganized every 2 weeks. With G
=
14% (14 days
×
1% per day),
Figure 11.7 suggests that P should be set to 30%.
Many tables are empty to begin with and then keep growing; the growth rate
initially may be very high. Indexes on such tables
need special treatment during
the first few weeks:
very high P (even 99%) and a reorganization possibly required
every night. Figure 11.7 can be used to predict how the reorganization interval
can be extended as the table grows and P is reduced. If the index discussed above
grows by 10,000 rows per day, it will have more than 10,000,000 rows in 3 years.
Then, with P
20%, the index reorganization interval can be as long as 80 days:
From Figure 11.7, for P
=
=
20%, G
=
8%; 8% of 10
,
000
,
000
=
800
,
000 rows,
equivalent to 80 days.
In this example, the length of an index row is 4% of the leaf page size (25
rows in total per page), about 160 bytes with 4K pages or 320 bytes with 8K
pages. In this case, P must be at least 20% to satisfy the five-row rule. This is
not a problem, but supposing the index rows were three times larger (12% of
the leaf page)? Leaving 60% of each leaf page empty after reorganization is not
an attractive thought; having only 3 index rows per leaf page would make index
Search WWH ::
Custom Search