What is the best way to delete all data from a table?

Question

I have a SQLite table with 6 million rows.

Doing DELETE FROM TABLE is pretty slow,

Deleting a table and then re-creating it seems faster.

I use this to import the database.

Will the table decline better, or is there a way to quickly delete all the data?

Answer 1

One big difference is that DELETE FROM TABLE is DML and DROP TABLE is DDL. This is very important when it comes to db transactions. The end result may be the same, but these operations are very different.

If this is just the performance that you should be aware of, then it may be nice to discard and recreate the table. If you need transactions in your import, you should know that DDL is not covered and, for example, there can be no rollback.

Answer 2

In general, DROP TABLE will be a transaction without registration. DELETE FROM will require the temporary logs to log entries until the DELETE statement is complete.

Answer 3

TRUNCATE TABLE is much faster.

The following, with the exception of the Oracle website, explains why:

Deletes the execution of normal DML. That is, they take row locks, they generate repetition (a lot), and they need segments in the UNDO tablespace. Removes clear records from blocks. If an error is made, a rollback may be issued to restore the records before committing. Deletion does not free up segment space, so the table in which all records were deleted retains all the original blocks.

Truncations are DDL and, in a sense, are deceiving. Truncation moves the high water sign of the table to zero. No row level locks are made, no repeated or rollback actions are generated. All extent limits, the original ones of which are not selected from the table (if you have MINEXTENTS set to anything other than 1, then this number of extents is saved, not just the initial one). By re-positioning the high water mark, they prevent the reading of any tabular data, so they have the same effect as deleting, but without all the overhead. Only one small problem: truncate is a DDL command, so you cannot roll it back if you decide you made a mistake. (It is also true that you cannot selectively trim -no "WHERE" is allowed, unlike deletions, of course).

By resetting the high water mark, truncate prevents any tabular data from being read, so it has the same effect as deleting, but without overhead. However, there is one aspect of Truncation to keep in mind. Since Truncate is DDL, it issues a COMMIT before it takes effect, and then another COMMIT, so the transaction cannot be rolled back.

Note that by default, TRUNCATE leaves memory even if DROP STORAGE is not specified.

Answer 4

I don't think SQLite implements TRUNCATE , but if it is likely to be more efficient than DELETE