Related to this issue ./heLen, the table field order can affect this
issue ?
TIA,
Doru

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 08:45 PM 3/04/2008, you wrote:
> >> This sounds like a compound PK where one (or more) segments have low
> >selectivity and mass inserts and deletes are being performed without
> >any follow-up housekeeping. If the PK is getting so out of balance,
> >it would be expected that dependent FK indexes would suffer the same
> >kind of deterioration.
> >>
> >> How often do you run SET STATISTICS on your indexes as a maintenance
> >measure? This won't rebuild indexes but it should help to improve the
> >selectivity of these unstable indexes.
> >>
> >
> >Thanks for the reples, ./heLen and Maxim!
> >
> >This is a 2 column primary key - id and date - and for most of the
> >id-s there's only one record and other id-s (about 1%) have 2-3
> >records with different dates. It seems to me that both id and date
> >fields have good selectivity.
> >
> >./heLen, you're right - the table was populated with mass inserts
> >without any later housekeeping - i've never used SET STATISTICS
> >actually :)
>
> Mass inserts probably account for large blocks of records with the
same date--> long chains.
>
>
> >At the moment i understand it like this: FB optimizer thought that my
> >PK index is no good, because index statistics was not up to date?
>
> Probably a combination of selectiveity not up-to-date and index
out-of-balance.
>
>
> >And assuming my mass inserts were random enough (not sorted by PK
> >fields) then index doesn't need rebuilding at all?
>
> Inserts are never sorted: the database engine doesn't understand
"sorted". In SQL engines "sorted" is an output concept, not a storage
concept. Inserts are simply performed in arrival order and nodes are
added to the index. Assuming the insert operation is writing
CURRENT_DATE to that node through a trigger,
> you will get a large batch of nodes having the same date.
>
> ./heLen
>