Hi!

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

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

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

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

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

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

Thanks,

Pepak