Subject Re: [firebird-support] Rebuilding primary key index?
Author Kjell Rilbe
Ann W. Harrison skriver:
> A Firebird index starts as a single page containing pairs of
> values and record identifiers. Eventually, someone wants to
> add another pair and it doesn't fit on the page. Firebird
> allocates a new page, copies half the pairs into the new page*,
> then allocates a second page with two entries, each consisting
> of a value, a record identifier, and a page number. The page
> numbers are the original index page and the new index page.
> The values and record numbers are the first pairs from the
> lower pages. The the system goes along, happily filling
> bottom level pages, splitting them as necessary, and adding
> new pages to the top page. Eventually, the top page fills,
> splits, and creates another top page with two entries, one
> for the first half of the former top page and one for the
> second.
> If you think about that for a second, you'll realize that
> the length from the top to every lowest level entry is exactly
> the same.
> Oh, but what about deleted records. Won't I have an unbalanced
> index if my key is always increasing with time and I delete the
> old records. The answer is no, because Firebird does almost
> exactly the reverse when pages get below 1/3 full - it combines
> them and removes the pointer from the next level up. That
> turns out to be very hard and has taken years of Vlad's life
> to make work under load as pages are added to and removed from
> indexes.

Thanks Ann. An excellent and detailed description. I found a similar
description, written by you, earlier today when searching on the subject.

So, when *IS* it relevant to rebuild a Firebird index, which I
understand happens when an index is deactivated and then reactivated?

Kjell Rilbe
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64