CODEWITHSUNDEEP
firebird
Trevor
Trevor

Reputation: 69

Set index statistics after insert in Firebird database

I would like to automate the process of setting index statistics in a Firebird database so that it doesn't require a database administrator to run the command, or a user to click a button.

Since the statistics only need to be recalculated after a large number of inserts or deletes, I am considering using an After Insert and After Delete trigger to keep track of how many inserts or deletes have taken place, and then run a procedure to set index statistics based on that value.

My question is whether there is anything to watch out for when setting the index statistics in this manner on a live database. To be clear, I am not rebuilding indexes, but recalculating index statistics only. It is quite possible that this would occur during a mass import or delete operation. Would calculating index statistics during a mass import or delete have the potential to cause any problems?

Upvotes: 2

Views: 1076

Answers (1)

tsteinmaurer
tsteinmaurer

Reputation: 409

It is safe to recalculate index statistics on a live database, while it is in use. It is also safe to do that in PSQL, e.g. in a stored procedure. For example I'm running a scheduled batch job in the night, which executes a stored procedure recalculating statistics for all indexes.

I'm not sure if it is wise to do that in a trigger, because triggers in Firebird fire per row and not per statement, thus you have to make sure to run that in some kind of conditional branch in your PSQL body.

Upvotes: 2

Related Questions