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

1 2 5 10

X
(No.of index rows that fit
in PCTFREE)

5,0

LPSR

4,5

4%

4,0

3,5

3%

3,0

2,5

2%

2,0

1,5

1%

1,0

Courtesy of
Matti Stahl

0,5

Figure 11.6 LPSR as a
function of used free space.

0,0

4 8 12 16 20 24 28 32 36 40 44 48 52 56 60 64

10% 20% 30% 40%

F (% of PCTFREE used)

So when should an index be reorganized? Let us assume that we want the
I/O time for an index slice scan to remain below 2 × ORIG . To achieve this, if

A/B = 100 the LPSR must remain below 1% :1 + ( 0 . 01 × 100 ) = 2.

1, LPSR becomes 1% when F is as low
as 15%, that is, when only 15% of the distributed free space has been used. This
could mean frequent reorganizations.
When X = 10, the LPSR stays below 1% until F becomes 48%. This appears
to be much better. However, if the index row length was 5% of the leaf page
size (about 200 bytes with 4K pages, 400 bytes with 8K pages), P would have
to be 50% to make X = 10. Sequential read would be relatively slow if all leaf
pages were only half full.
With the common leaf page sizes currently in use (4K and 8K), X = 5 seems
a reasonable compromise between reorganization frequency and the amount of
distributed free space required. We will base our general recommendations on
X = 5 . When the index rows are exceptionally long, we will have to consider
smaller X values, as we shall see in due course.
Figure 11.6 shows that when X = 5, we should reorganize an index when no
more than 36% of the distributed free space has been used, in order to achieve
an LPSR below 1%; for simplicity, we will take a figure of one third, 33%. But
when will this occur? How long will it be before one third of the distributed
free space has been used, at which point the index will need to be reorganized
to achieve our stated objectives?
As F represents a percentage of the distributed free space , we will need to
convert this to a percentage of the actual data ; this is, of course, the non-free-
space area. With the figures from our example (P = 20%, F = 33%), the point
at which reorganization should take place will be reached when further growth
(we will call this the Growth before REORG, G) of:
F × ( P /( 100 − P )) = 33 × ( 20 /( 100 − 20 ))
= 8 . 3%

Referring to Figure 11.6, when X =