Subject Re: [Firebird-Architect] Clustered indexes
Author Jim Starkey
Ann W. Harrison wrote:

>So, the additions to "over my dead body" are
>1) that we have a mechanism described by Dmitry that is efficient for
>all indexes, not just a clustered index
>and 2) that clustered indexes work badly unless very well tuned in
>non-multi-generational database, and even worse in a multi-generational
I have more.

If the space allocation and record distribution is not perfect, the
performance of non-indexed retrievals is degraded by the amount of
unused space. The performance effect of non-primary index retrieval is
more difficult to analyse because it's always bad, but assuming
secondard indexes are orthogonal to the primary, cluster effectively
guarantees pessimal performance on secondary indexes.

With bitmap oriented retrievals, Firebird can optimize two indexable
conjunction into two scans and a bitmap "and" operation before fetching
records. With a cluster index strategy, the optimizer is required to
pick a single index to drive retrieval, increasing the complexity of the
optimizer and increasing its sensitivity to error based on incomplete
knowledge of data demographics.


Jim Starkey
Netfrastructure, Inc.
978 526-1376