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.
The Oracle Sponge 
Hi again David – good to see you posting again.
truncate also discards an objects segments and re-allocates a new one.
You can see this by watching the value of user_objects.data_obj_id change after each truncate ( user_objects.data_obj_id – the object_id of the segment that contains the object)
This is part of the reason why truncate can be so expensive to carry out in a high transaction rate system.
Best Regards,
Mathew Butler
Yes indeed — although if you have never inserted any rows into the table then a truncate doesn’t change the data object id (in 10g at least). Even a single insert that is then rolled back causes a data object id change on truncate.
I suppose the usual reason people would be truncating in an OLTP system would be to do with temporary table usage, or something like that, where a global temporary table (or subquery factoring clause) would often do the job just as well.
Truncate will also invalidate any cursors referencing that table.
Dom — ah, that’s interesting. Do you know if that’s documented anywhere?
David:
Point 3 and others…
I assume this is for Oracle. I recall I did undo on a truncate table on SQLServer 2005 just a few months ago…
Cheers.
Carlos.
Oh yes — I’m exclusively an Oracle wonk. Maybe I should change the title …
I remember that when a company I worked for a long time ago changed from Ingres to Oracle there was much hilarity when we discovered that a truncate couldn’t be rolled back.
Dom’s point is good. Had a major performance issue in some code which had a loop including a truncate ‘because it is faster than a delete’. All the cached SQL was invalidated (because the table was subjected to DDL) and required re-parsing. Man, that code ran SLOW.
Another issue recently discussed is that truncate doesn’t reset statistics for the table/indexes. It’d be nice if a clause such as “reset statistics” will be added for the “truncate table” command.
Reposting for a hopefully readable comment.
Another issue recently discussed here is that truncate doesn’t reset statistics for the table/indexes. It’d be nice if a clause such as “reset statistics” will be added for the “truncate table” command.
Yes, that’s true. As you say, a handy option rather than something you’d want applied by default.
It’s not a difference per se, but is an area of commonality between delete and truncate. In fact there’s an explicit commonality as both operations are logged as table modifications if the table has the “monitoring” flag set against it.
David did you see this?
http://www.freelists.org/archives/oracle-l/03-2008/msg00493.html
Very funny!
***********************************************
In this scenario you just truncated a table in production that you
meant to truncate in development. Now what do you do?
1) Shout OHHH SH**! (fill in the ‘*’ with whatever letters you think
are appropriate)
2) After you’ve gotten over the initial shock of this monumental mistake,
do a
Shutdown immediate;
3) Stop, think and immediately tell your manager or coworkers what you did.
4) Curse a coworker who had nothing to do with YOUR mistake. It won’t
help the situation, but now you can feel bad for two stupid mistakes.
5) Apologize for cursing your coworker and accept FULL responsibility
for YOUR mistake.
6) Now that you are rational, calmly discuss with your
manager/coworkers the best course of action. If it is decided to
proceed with a point-in-time recovery, then continue with the
following steps.
7) Backup the controlfile in case you have to restart the recovery:
Alter database backup controlfile to
‘$ORACLE_HOME/dbs/$ORACLE_SID_backup.ctl’;
8) Check the alert log for the exact time you started the “shutdown
immediate”.
9) Think about how much time may have passed between step 1 and 2,
then decide on a safe point-in-time before the truncate occurred.
10) Run ‘rman’.
RMAN> connect target /
RMAN> restore database;
— After restore completes
RMAN> exit
11) Run ‘sqlplus “/ as sysdba’
SQL> recover database until time ‘YYYY-MM-DD:HH24:MI:SS’;
SQL> alter database open resetlogs;
–shutdown may take a while to apply undo from recovery.
SQL> shutdown immediate;
SQL> Startup restrict;
12) Check to make sure everything looks OK.
13) If everything looks good, then open the database to the users:
SQL> shutdown immediate;
SQL> startup;
14) Thank your coworkers for all their help and be grateful you still
have a job.
Always easy to be wise in hindsight, but preventing an accidental truncation is sometimes just as easy as disabling table locks as long as you don’t need table locks in your regular application processing.
> you know if that’s documented anywhere?
I’ve observed the behaviour, not sure I’ve read it definitively.
Just did a quick search of various sources.
They are hardly the most convincing of articles – their style and spelling and grammatical mistakes detracting from their authority, but from Metalink note 62143.1 “Understanding and Tuning the Shared Pool”:
”
Avoid Invalidations
Some specific orders will change the state of cursors to INVALIDATE. These orders modify directly the context of related objects associated
with cursors. That’s orders are TRUNCATE, ANALYZE or DBMS_STATS.GATHER_XXX on tables or indexes, grants changes on underlying objects. The associated cursors will stay in the SQLAREA but when it will be reference next time, it should be reloaded and reparsed fully, so the global performance will be impacted.
”
and also metalink note 123214.1 – “Truncate – Causes Invalidations in the LIBRARY CACHE”.
It’s an interesting feature for sure. It makes sense in some ways, but since a truncate is not going to change the table statistics one also wonders why it’s there. Maybe the truncate command ought to come with a “no invalidate” option, like dbms_stats gathering procedures.
Oh, maybe the reason it’s there is because of the change in the data object id. Maybe that is significant.
My interpretation:
The change in data object id signifies that the objects segments have been discarded. So, although the objects stats have not been affected – they would no longer reflect the data stored in the table. And in some senses this is really a new object ( as far as cached cursors are concerned ).
I expect that the optimizer avoids making any assumptions about the state of the object before the truncate by invalidating referencing cursors.
Given this some more thought…
I think it has nothing to do with the table stats. It’s purely that truncate is DDL, and I hypothesise that the optimizer just doesn’t know that kind of DDL and so has to re-parse cursors to ensure that the table can still support the cached query.
Hi David,
I’ve seen you publish some lists on your blog in the past, so I was wondering if you have a list of interview questions you have asked ETL developers? I’m trying to come up with a list that is not too tool-specific (we use DataStage ourselves), and I’m debating if it should be Oracle-heavy. Thoughts on that?
Thanks.
Kashif
Hmm, let me think about that. I do have a list somewhere. If I don’t post it in the next couple of days send me a reimnder … :)
Will do, thanks.
Kashif
So if the truncate syntax goes like
TRUNCATE { TABLE [ schema. ]table
[ { PRESERVE | PURGE } MATERIALIZED VIEW LOG ]
| CLUSTER [ schema. ]cluster
}
[ { DROP | REUSE } STORAGE
Where it is really not mandatory to add (DROP STORAGE) to the truncate statement knowing fully well the Watermark is dropped. Lately some of my Truncate statements that are part of Plsql package have been failing randomly with a ORA-03291 error. I iteratively walk through a list to truncate some tables and 1 or 2 of 10 randomly fail. But work fine when I rerun. Its been a night mare for couple of weeks now. Any thoughts please.
ORA-03291: Invalid truncate option – missing STORAGE keyword
ORA-06512: at “xxxx.xxxxxxxxx”, line 35
ORA-06512: at line 2
Thanks
I’ve only ever seen that error where there was a typo. What’s the complete command you’re using?
You will get better and easy explanation over here:
http://dotnetpeoples.blogspot.com/2011/04/difference-between-delete-and-truncate.html
@Jatin,
I have no problem with people putting in links to other genuinely helpful resources, but you’re referencing as “better and easier” a blog post which:
i) is just a five-point list of differences.
ii) does not reference which RDBMS it relates to.
iii) is probably wrong in respect of rollbacks and triggers.
TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.
You can use WHERE clause(conditions) with DELETE but you can’t use WHERE clause with TRUNCATE .
Truncate an Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure.Both statements delete the data from the table not the structure of the table.
TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.
You can use WHERE clause(conditions) with DELETE but you can’t use WHERE clause with TRUNCATE .
You cann’t rollback data in TRUNCATE but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.
A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.
If tables which are referenced by one or more FOREIGN KEY constraints then TRUNCATE will not work.
TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.
Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists.
TRUNCATE is faster than DELETE.