Hello,

> 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?

Are you sure that the index stuff is your problem and not transaction
related? What's the output of gstat -h at the time you think the
database is getting slow?


--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!