Firebird trims table / deletes all rows

Question

I am using Firebird 2.5.1 Embedded. I did the usual to clear a table with almost 200 thousand lines:

delete from SZAFKI 

Here's the conclusion, as it takes 16 seconds, which, unfortunately, is unacceptable.

 Preparing query: delete from SZAFKI Prepare time: 0.010s PLAN (SZAFKI NATURAL) Executing... Done. 3973416 fetches, 1030917 marks, 116515 reads, 116434 writes. 0 inserts, 0 updates, 182658 deletes, 27 index, 182658 seq. Delta memory: -19688 bytes. SZAFKI: 182658 deletes. 182658 rows affected directly. Total execution time: 16.729s Script execution finished. 

Firebird does not have the keyword TRUNCATE. Since the query uses PLAN NATURAL, I tried to execute the query manually, for example:

 delete from szafki PLAN (SZAFKI INDEX (SZAFKI_PK)) 

but Firebird says: "SZAFKI_PK cannot be used in the specified plan" (this is the primary key) Question: how to clean the table efficiently? Falling and recreating is impossible.

Answer 1

Answer based on my comment

The trick you could try is to use DELETE FROM SZAFKI WHERE ID > 0 (if the ID is 1 or higher). This will make Firebird search for strings using the primary key index.

My initial assumption was that it would be worse than unindexed delete. Unindexed delete will perform a sequential scan of all types of table data and delete rows (i.e.: create a new record, which is a remote stub record). When you use an index, it will search for rows in index order, this will lead to random passing through the data (assuming a high level of data fragmentation due to the large number of record versions due to insertions, deletions and updates). I expected this to be slower, but it is likely to cause Firebird to only read the relevant data types (with transaction versions of the records), and not all the data in the table.

Answer 2

Unfortunately, there is no quick way to do a bulk deletion of an entire (large) table with Firebird versions currently. You can expect even more delays when the “deleted content” collects garbage (run select * in the table after the deletion is committed and you will see). You can try to deactivate the indexes in this table before deleting and see if it helps. If you use the table as a kind of temporary storage, I suggest you use the GTT function.

Answer 3

The fastest and only way point to get rid of all data quickly in the FireBird table is drop and create again. At least for the current official version 2.5.X. There is no truncation operator in the roadmap for FireBird 3.0, there is no beta, so there is most likely no truncation in 3.0.

Alternatively, you can use the RECREATE statement , the same syntax as create. If the table exists, RECRATE discards it and then creates a new one. If the table does not exist, then creating it simply creates.

  RECREATE TABLE Table1 ( ID INTEGER, NAME VARCHAR(20), DATE DATE, T TIME );