One of the often task in ETL applications is to quick erase all data in some table(s). Often amount of data is big or huge (tens of millions records). In Firebird we have two choices currently – DELETE all rows or RECREATE TABLE. Both have its own drawbacks. DELETE produced a lot of record versions to clean up, its slow as works row-by-row basis and fire triggers which is almost always is not needed. RECREATE is free from this drawbacks but it will fail if there is dependencies and all related objects such as triggers, indices, constraints must be recreated too.
SQL 2008 introduced “new” statement free from this drawbacks : TRUNCATE TABLE. This
statement is long time present in commercial DBMS’s and currently it is part of standard. I offer to implement it in Firebird too.
Let’s look at standard.
14.10 <truncate table statement>
Function
Delete all rows of a base table without causing any triggered action.
Format
<truncate table statement> ::=
TRUNCATE TABLE <target table> [ <identity column restart option> ]
<identity column restart option> ::=
CONTINUE IDENTITY
| RESTART IDENTITY
Syntax Rules
1) Let TN be the <table name> contained in the <target table>. Let T be the table identified by TN. The schema identified by the explicit or implicit <schema name> of TN shall include the descriptor of T.
2) T shall be a base table.
3) T shall not be identified by the name of the referenced table in any referential constraint descriptor.
4) If <identity column restart option> is not specified, then CONTINUE IDENTITY is implicit.
Access Rules
1) Let A be the <authorization identifier> that owns the schema identified by the <schema name> of the table identified by TN.
2) The enabled authorization identifiers shall include A.
General Rules
1) If the access mode of the current SQL-transaction or the access mode of the branch of the current SQLtransaction at the current SQL-connection is read-only, and T is not a temporary table, then an exception condition is raised: invalid transaction state – read-only SQL-transaction.
2) If there is any sensitive cursor CR that is currently open in the SQL-transaction in which this SQL-statement is being executed, then
Case:
a) If CR has not been held into a subsequent SQL-transaction, then either the change resulting from the successful execution of this statement shall be made visible to CR or an exception condition is raised:Ā cursor sensitivity exception – request failed.
b) Otherwise, whether the change resulting from the successful execution of this SQL-statement is made visible to CR is implementation-defined.
3) If there is any open, insensitive cursor CR, then either the change resulting from the successful execution of this statement shall be invisible to CR, or an exception condition is raised: cursor sensitivity exception – request failed.
4) The extent to which an SQL-implementation may disallow independent changes that are not significant is implementation-defined.
5) Case:
a) If <target table> contains ONLY, then the rows for which there is no subrow in a proper subtable of T are deleted from T.
b) Otherwise, all rows are deleted from T.
6) If any row that is deleted from T by this <truncate table statement> has been marked for deletion by any <delete statement: positioned>, <dynamic delete statement: positioned>, or <preparable dynamic delete statement: positioned> that identifies some open cursor CR or updated by any <update statement: positioned>, <dynamic update statement: positioned>, or <preparable dynamic update statement: positioned> that identifies some open cursor CR, then a completion condition is raised: warning – cursor operation conflict.
7) If no rows are deleted from T, then a completion condition is raised: no data.
8) If RESTART IDENTITY is specified and the table descriptor of T includes a column descriptor IDCD of an identity column, then:
a) Let CN be the column name included in IDCD and let SV be the start value included in IDCD.
b) The following <alter table statement> is effectively executed without further Access Rule checking:
ALTER TABLE TN ALTER COLUMN CN RESTART WITH SV
Few notes about level of support of TRUNCATE statement in Firebird :
a) as we have no IDENTITY we can ignore <identity column restart option> and all related rules
(Syntax rule 4, Genereal rule 8)
b) Syntax rule 2 means TRUNCATE TABLE is not applicable to 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.
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.
Regards,
Vlad
« Hide it
