Informix DBA Survival Guide, Second Edition

Clean out Tables

There are two distinct parts to the problem of cleaning out tables. The first part is knowing that the tables need to be purged. The second part is the mechanical question of how to do it.

We'll cover the mechanical aspects here. Look to the sections on database monitoring and table proliferation for methods of discovering which tables need to be cleaned.

Assuming that you know that table x needs to be purged, how do you go about it? If you have determined that the table needs to be completely emptied, you may be tempted to use this SQL statement;

DELETE FROM x;

to remove all of the rows. This is OK for a small table, but it can take a significant amount of time on larger tables. To completely empty a large table, it is easier ...