Please see:

http://www.firebirdnews.org/?p=5048
http://www.firebirdnews.org/?p=5051

Also, note that building indexes while people are manipulating records
on the table seems to not be "safe" in some of the older FB versions
(pre 2.0, I think).

Look at Thomas answers too.

Carlos
Firebird Performance in Detail - http://videos.firebirddevelopersday.com
www.firebirdnews.org - www.FireBase.com.br

P> Hi!

P> Is there a way to completely regenerate all indices in a database
P> without running through the full backup/restore cycle? It's been my
P> experience that over months or years, the database keeps getting slower
P> and slower, but a backup and immediate restore fix it to run at
P> full-speed again. I would like to achieve the same result without having
P> to stop all other work. What I tried so far:

P> - Recalculate statistics on all indices (FOR SELECT rdb$index_name FROM
P> rdb$indices INTO :name DO SET STATISTICS INDEX :name). It seemed to have
P> a small effect so I started to run it automatically every month or so,
P> but the effect is quite small and eventually a full backup/restore is
P> needed again.

P> - One after another, deactivate each index and reactivate it (FOR SELECT
P> rdb$index_name FROM rdb$indices INTO :name DO BEGIN ALTER INDEX :name
P> INACTIVE; ALTER INDEX :name ACTIVE; END). I may have done something
P> wrong, but this tended to make things even worse.

P> - Backup and restore. This works great, but requires an exclusive access
P> to the database (for the restore part, and it really is needed even for
P> the backup since I don't want someone to add new records while I am
P> backing up) and anyway, my users aren't quite happy that this needs to
P> be done every once in a while.

P> I am using various Firebird versions starting with 1.5 and going up to
P> 2.5, but I would be happy enough with a solution which works on some of
P> these versions, not necessarily all of them.

P> Can anyone point me to some obscure argument in gbak or gfix which I
P> have missed, or some other solution which could be used at runtime?

P> Thanks,

P> Pepak