Subject RE: [firebird-support] Re: How to know when an index has become unbalanced?
Author Alan McDonald
> --- In, "Alan McDonald" <alan@...> wrote:
> >
> > > The subject pretty much sums up the question, "How to know when an
> > > index has become unbalanced?"
> > >
> > > I know how to rebuild an index and update the statistics, but how can
> > > I know the state of the index itself and whether it needs rebuilding?
> > >
> > > Example: I have a primary key on an autoincrement column. I'd assume
> > > the index tree would get unbalanced rather quickly - - right?
> > >
> > > Thanks
> > > Atli
> >
> > if you've done a lot of mass inserts and/or deletes, I would set stats.
> > Otherwise I would leave my indexes alone until normal backup/restore
> type
> > maintenance operations.
> > PK fields don't go out of balance quickly - quite the reverse.
> > Alan
> >
> Thanks,
> Doesn't Firebird create an index automatically on primary key fields?
> Is that index maintained in a different manner than other indices?

yes if you decalare a field as a primary key and therefore it aquires
constraint status, and no - it's the same as any other unique index or

> Anyhow, is there any way to know if an index has become unbalanced (in
> a similar way to how I can see the statistics for an index)? Or is
> this a non-issue in general?

there is no other way - the statistics is THE way to see. In most
circumstances it is a non-issue. If your systems are performing well, there
is no requirement that you even inspect the statistics. If you feel that
your performance is degraded, you may check it. IME, poor performance is
more often from a missing index than it is from an unbalanced one ans as I
said, normal maintenance cycles of annual backup/restore is good enough
unless your system is designed (architected) to undergo massive deletes and
inserts on a regular basis. In this case I would inspect regularly and
either B/R more regularly or rebuild indexes more regularly. You can even
set a scheduled script to do it if you are really concerned. But in most
cases it's just a waste of time.


> Thanks
> Atli