Few notes about level of support of TRUNCATE statement in Firebird :
a) as we have no IDENTITY we can ignore
(Syntax rule 4, Genereal rule
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
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.
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.
Firebird說明仍能有條件地使用 TRUNCATE 指令