SQL Firebird 截断表/删除所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13818194/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Firebird truncate table / delete all rows
提问by Kitet
I am using Firebird 2.5.1 Embedded. I have done the usual to empty the table with nearly 200k rows:
我正在使用 Firebird 2.5.1 Embedded。我已经按照惯例清空了将近 20 万行的表格：
delete from SZAFKI
Here's the output, see as it takes 16 seconds, which is, well, unacceptable.
这是输出，看看它需要 16 秒，这是不可接受的。
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 has no TRUNCATE keyword. As the query uses PLAN NATURAL, I tried to PLAN the query by hand, like so:
Firebird 没有 TRUNCATE 关键字。由于查询使用 PLAN NATURAL，我尝试手动计划查询，如下所示：
delete from szafki PLAN (SZAFKI INDEX (SZAFKI_PK))
but Firebird says "SZAFKI_PK cannot be used in the specified plan" (it is a primary key) Question is how do i empty table efficiently? Dropping and recreating is not possible.
但是 Firebird 说“SZAFKI_PK 不能在指定的计划中使用”（它是一个主键） 问题是我如何有效地清空表？删除和重新创建是不可能的。
回答by Mark Rotteveel
Answer based on my comment
根据我的评论回答
A trick you could try is to use DELETE FROM SZAFKI WHERE ID > 0(assuming the IDis 1 or higher). This will force Firebird to look up the rows using the primary key index.
您可以尝试使用的一个技巧是DELETE FROM SZAFKI WHERE ID > 0（假设ID是 1 或更高）。这将强制 Firebird 使用主键索引查找行。
My initial assumption was that this would be worse than an unindexed delete. An unindexed delete will do a sequential scan of all datapages of a table and delete rows (that is: create a new recordversion that is a deleted stub record). When you use the index it will lookup rows in index order, this will result in a random walk through the datapages (assuming a high level of fragmentation in the data due to a high number of record versions due to inserts, deletes and updates). I had expected this to be slower, but probably it will result in Firebird having to only read the relevant datapages (with record versions relevant to the transaction) instead of all datapages of a table.
我最初的假设是这会比未编入索引的删除更糟糕。未索引的删除将对表的所有数据页进行顺序扫描并删除行（即：创建一个新的记录版本，即已删除的存根记录）。当您使用索引时，它将按索引顺序查找行，这将导致在数据页中随机游走（假设由于插入、删除和更新导致的大量记录版本而导致数据中的高度碎片化）。我原以为这会更慢，但可能会导致 Firebird 只读取相关数据页（具有与事务相关的记录版本）而不是表的所有数据页。
回答by WarmBooter
Unfortunately, there is no fast way to do massive delete on entire (big) table with currently Firebird versions. You can expect even higher delays when the "deleted content" is garbage collected (run select * in the table after the delete is committed and you will see). You can try to deactivate indexes in that table before doing the delete and see if it helps. If you are using the table as some kind of temporary storage, I suggest you to use the GTT feature.
不幸的是，目前的 Firebird 版本没有对整个（大）表进行大规模删除的快速方法。当“已删除的内容”被垃圾收集时，您可以期待更高的延迟（在提交删除后在表中运行 select * ，您将看到）。您可以在删除之前尝试停用该表中的索引，看看是否有帮助。如果您将表用作某种临时存储，我建议您使用 GTT 功能。
回答by TPAKTOPA
Fastestand only way to dot get rid from all data fastin FireBird table- dropand createtable again. At least for the current official version 2.5.X . There is no truncate operator in roadmap for FireBird 3.0 , beta is out, so most probably no truncate in 3.0 too.
在 FireBird表中快速删除所有数据的最快也是唯一的方法-删除并再次创建表。至少对于当前的官方版本 2.5.X 。FireBird 3.0 的路线图中没有截断运算符，测试版已经出来，所以很可能在 3.0 中也没有截断。
Also, you can use the operator RECREATE- same syntax as create. If table exists, RECRATE drops it, then creates new. If table doesn't exists, then recreate just creates it.
此外，您可以使用运算符RECREATE- 与 create 的语法相同。如果表存在，RECRATE 删除它，然后创建新的。如果表不存在，则重新创建只是创建它。
RECREATE TABLE Table1 (
ID INTEGER,
NAME VARCHAR(20),
DATE DATE,
T TIME
);
