Leyne, Sean wrote:

>
> The technical term "getting of out sync" I think is a "red-herring". I
> think what was being discussed is the fact that indexes get "out of
> balance" or change their selectivity, meaning their storage is NOT
> always optimized for speed.
>
> This was true of earlier version of IB, but was addressed in v5.?
> through the introduction of self-optimizing indexes.
>

Markus mentioned that IB has dynamic index page garbage collection. This
means that empty index pages are removed, two adjacent index pages can
be combined into one index page, and index levels are removed top-down
if fill factors warrant.

In addition, there is ALTER INDEX <indexname> SELECTIVITY which performs
an online, non-destructive traversal of the leaf level to recompute
index selectivity. This feeds back into the query optimizer's
statistics.

As Jim mentioned in a different post, record versioning can perturb the
selectivity recalculation if the version updates cause modifications to
the index. My gut feel is that most updates don't modify columns
participating in index keys but there are always exceptions to the norm.

> IB has, since v4 (at least), provided a mechanism to force the system to
> rebuild the index information (ALTER INDEX INACTIVE, followed by ALTER
> INDEX ACTIVE). This could be performed while the system was in use.
>
> It seems that Oracle is just catching up to IB.
>
> Sean
>

Won't touch that last statement. In the interest of fairness, INSERT,
DELETE, and UPDATE operations on the index's table will stall until the
transaction rebuilding the index commits. SELECT operations are
impervious and will execute unfazed.

Regards,
Charlie