I interviewed a couple of senior ETL developer candidates last week, so I shook the dust off of some of my tried-and-true probes for technical knowledge.
I’ve always had a liking for asking for the differences between “truncate” and “delete” ever since I was asked it myself some years ago. I think it allows candidates to demonstrate a nice range of knowledge.
Here are some answers in no particular order.
-
“Truncate is fast, delete is slow”. Hmmm, well that’s mostly true I guess. The most basic or all answers.
-
“Truncate is DDL, delete is DML”. Is this just so obvious that people don’t mention it?
-
“You can’t rollback a truncate”. Indeed. See 2 above, of course. And 1.
-
“Truncate is implicitly a commit”. A better answer than 3 I think. I seem to recall that someone once mentioned that there are actually two commits in a truncate, one before and one after … but maybe I dreamed that. I should test it really.
-
“You can’t grant permission to truncate a table”. Ah, practical experience shines through. If you don’t like your ETL process to connect as the owner of the schema then this is a challenge that has to be overcome with stored procedures or something sophisticated like that. You really don’t want to grant “DROP ANY TABLE” to your ETL user.
-
“You can delete any subset of rows, but you can only truncate the complete table, or a partition or subpartition of it”. Is this also so obvious that nobody mentions it?
-
“Truncate makes unusable indexes usable again”. A real gotcha for the unwary. If you attempt to optimise a data load by rendering indexes unusable and truncating a table (possibly followed by an index rebuild and a partition exchange) then be careful of the order.
-
“Truncate can’t maintain foreign keys”. It’s “cascading delete”, not “cascading truncate”. That would be an interesting feature though — point 4 above would make it a little trickier. Truncating an index cluster is pretty close to a “cascading truncate” to a limited extent though. In any case no truncate is permitted on a table referenced by foreign keys.
-
“You can’t flashback a truncate”. This is an oddity to me. We can flashback a “drop table”, rollback uncommited deletes, or use flashback to recover pre-commit deleted data, but a truncate is a barrier across which we cannot flashback.
-
“Truncate deallocates space, delete doesn’t”. Unless you want it not to, using the “reuse storage” clause. However the high water mark is reset in either case so maybe that’s a better answer …
-
“Truncate resets the high water mark, delete doesn’t”. And on the indexes, also. See 7 above also.
-
“Truncate allows special treatment of materialized view logs”. More of that practical experience and/or documentation-reading skill shows through.
-
“DML triggers do not fire on a truncate”. Because … um … it’s DDL not DML.
I suppose there are other issues as well, but this shows that it’s a complex business. I really wish we could grant “Truncate table” to a user or role … enhancement request time I think.
Isn’t it interesting that in some circumstances only one method can be used to remove all rows from a table in a single step, sometimes both methods can. In other circumstances both methods can remove a subset of rows from a table, and sometimes both can.
Oh well, back to work.
