k_smith_000 wrote:

> I've a table with a large number (200 000 000) of records. I'd like to
> remove ~90% of records from table that fulfill given criteria.
>
> I know such operation can take a long time and I should backup and
> restore database after operation has finished.
>
> My question is, what is the most efficient way of performing such
> operations. Should I put one delete statement into one transaction, or
> divide it into few statements with seperate transactions?
>
> Any help would be appreciated!
>

1) Disable user access of the database.
2) Do a full backup (before deleting anything).
3) Do a restore under another name (this becomes the working copy).
4) Disable all triggers and indexes on the working copy
5) Run your delete on the working copy
6) Re-enable triggers and indexes on the working copy
7) Do a backup of your working copy
8) Restore the backup of the working copy creating the final copy
9) Shutdown the Firebird server.
10) Delete the working copy.
11) delete the original copy
12) rename the final copy to the original name.
13) Restart the Firebird server machine


The delete process may not be the most efficient, BUT this method means
that the whole process is reversible. If something heads south, you can
always go back to the original point. In fact up until step 11, you
have not touched the original database at all. Step 13 is really
optional, although it makes sure that all the caches and buffers have
been cleared, and that all the file chains are up to date.

Speaking of chains, I have an appointment with my Mountain Bike!

HTH

W