Firebird 不支援 TRUNCATE TABLE 指令

來源：http://www.firebirdnews.org/?p=2075=1
以下節錄：

Few notes about level of support of TRUNCATE statement in Firebird :

a) as we have no IDENTITY we can ignore and all related rules
(Syntax rule 4, Genereal rule

Firebird中沒有IDENTITY，所以不用找了 (笑)

b) Syntax rule 2 means TRUNCATE TABLE is not applicable to VIEW’s

TRUNCATE TABLE 不能用在VIEW's

c) Syntax rule 3 means truncated table must not participate in FOREIGN KEY constraints as
master table.
ORACLE relaxed this rule and allow self-referenced constraints. I think we can follow it.

d) as we have no subrows we can ignore General rule 5

e) it will be good to implement TRUNCATE privilege to satisfy Access rules but for the first time
we can allow to TRUNCATE table only for SYSDBA and OWNER.

Firebird資料庫有優異的TRUNCATE存取規則，但初步我們只開放「SYSDBA」和「OWNER」有TRUNCATE權限。

ORACLE used DROP TABLE privilege to control usage of TRUNCATE TABLE statement, while MSSQL used ALTER TABLE privilege.

From the implementation point of view i have some concerns. All the code which released
table’s and indexes pages without removing relation itself is already present. It may require
some small changes but i see no problem with it.

The main concern is about rollback’s (or undo). If we will perform TRUNCATE TABLE as usual
DML statement, i.e. not defer it to transaction commit time, then we must implement support
for undo-log. Also we must decide if we will preserve truncated contents for older still active
concurrent transactions. And specify when this content will be erased finally.

Another approach is to require exclusive lock on table when TRUNCATE is issued (to prevent
any concurrent access to table’s data) and to defer TRUNCATE execution to the commit time. It
allows us to not preserve table contents for the indefinite time and to avoid complex manipulations with undo-log.

Note, ORACLE can’t rollback TRUNCATE statement. MSSQL 2005 don’t log truncation of big
objects and defer physical deallocation until commit time. All pages remains locked until commit
so no concurrent access to truncated data is allowed, AFAIU. IIRC, MSSQL before 2005 also can’t
rollback TRUNCATE statement.

看來是為了安全性的考量…

(20090429修改--始)

Firebird說明仍能有條件地使用 TRUNCATE 指令

但我怎麼樣都試不出來……

(20090429修改--完)

要刪資料？只能慢慢刪或重建table了…Orz