Delete on batch

Question

I have a database table that has >14 million rows.

If I attempt to perform:

 delete from table

The connection hangs out.

How can I delete all the rows, in "batches" so they are deleted eventually?

Answer 1

Try out:

truncate table tblname

This has the advantage of not being rollback-able so will not have to log its actions, and also does not fire triggers. That makes it substantially faster than the equivalent delete from tblname.

Otherwise, you can work out subsets to delete, based on your table data. For example, if there's a field (hopefully indexed) containing last names:

delete from tblname where lastname like 'A%'

Note that this is just an example. You can also use things like

• salary (<20K, 20K-40K, 40K-60K, ...).
• SSN.
• current balance.

and so on. What you choose depends entirely on the table you're trying to delete.

I prefer the truncate command myself due to its simplicity.